为什么我的EF似乎从正在运行的SQL视图中返回重复的行? [英] Why does my EF appear to return duplicate rows from my SQL View which is working?
问题描述
我已经查找了问题，但没有发现对我有用.我在SQL中创建了一个视图，当您在Management Studio中运行该视图时，该视图将起作用.从我的MVC应用程序访问该视图时，EF返回的是相同的行，而不是包含不同数据的行.
I've looked the question up but nothing I have found is working for me. I created a view in SQL which works when you run it in the Management Studio. When the view is accessed from my MVC Application, EF is returning identical rows instead of rows with different data.
表格:汽车
- [Id]
- [注册]
- [制作]
- [型号]
表格:预订
- [Id]
- [BookingStartDate]
- [BookingEndDate]
- [CarId]
查看:CarBookings
SELECT [C].[Id],
[C].[Registration],
[C].[Make],
[C].[Model],
[B].[BookingStartDate],
[B].[BookingEndDate]
FROM [Cars] AS C INNER JOIN [Bookings] AS B ON C.Id = B.CarId
如果我在SSMS中运行查询，则会得到所有预期结果，例如:
If I run the query in SSMS I get all the expected results, for example:
- 汽车1，预订12/03/2018
- 汽车1，预订于19/09/2018
当我从MVC应用程序访问相同的视图时，我得到:
When I access the same view from my MVC Application I get:
- 汽车1，预订12/03/2018
- 汽车1，预订12/03/2018
在控制器上放置一个断点表明结果是相同的，因此不是表示层引起的.没有应用任何过滤器，也没有任何条件.
Putting a breakpoint onto the controller shows that the results are the same so it's not the presentation layer that's causing it. No filters are applied and there are no conditions at all.
我正在使用 KendoUI ，并将结果返回到 Grid .
I'm using KendoUI and returning my results to a Grid.
这是我获取数据的控制器代码:
Here is my controller code for getting the data:
HomeController.cs
public ActionResult GetBookings([DataSourceRequest] DataSourceRequest request)
{
var bookings = unitOfWork.BookingsRepository.Get();
var result = bookings.ToDataSourceResult(request);
return Json(result, JsonRequestBehavior.AllowGet);
}
我的应用程序使用通用存储库.我不确定是否是造成问题的原因，但值得一提.这是我的存储库中的 GET 方法.
My application uses a generic repository. I'm not sure if it's causing the problem but it's worth mentioning. Here is the GET method from my repository.
DAL/GenericRepository.cs
public virtual IEnumerable<TEntity> Get(
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
string includeProperties = "")
{
IQueryable<TEntity> query = dbSet;
if (filter != null)
{
query = query.Where(filter);
}
foreach (var includeProperty in includeProperties.Split
(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
query = query.Include(includeProperty);
}
if (orderBy != null)
{
return orderBy(query).ToList();
}
else
{
return query.ToList();
}
}
DAL/Context.cs
public DbSet<Bookings> Bookings { get; set; }
DAL/UnitOfWork.cs
private GenericRepository<Bookings> bookingsRepository;
public GenericRepository<Bookings> bookingsRepository
{
get
{
if (this.bookingsRepository == null)
{
this.bookingsRepository = new GenericRepository<Bookings>(context);
}
return bookingsRepository;
}
}
实体类
这是代表视图并使用 [Table] 批注访问它的类.
This is the class that represents the view and accesses it using the [Table] annotation.
namespace MyProject.Models
{
[Table("CarBookings")]
public class Bookings
{
//Car
[Key]
public int Id { get; set; }
public string Registration { get; set; }
public string Make { get; set; }
public string Model { get; set; }
//Booking
public DateTime BookingStartDate { get; set; }
public DateTime BookingEndDateYearOfBuild { get; set; }
}
}
搜索此问题的答案时，我发现该视图没有 ID ，因此EF尝试按唯一值对记录进行逻辑排序，这有时会引起问题(来源:
When I searched for answers to this, I read that the view doesn't have an ID so EF tries to logically order records by unique values and this can sometimes cause problems (source: https://www.itworld.com/article/2833108/development/linq-in--net-returning-duplicate-rows-from-a-working-sql-view--solved-.html).
我已根据上述文章调整了视图的 select 代码，但对我而言无效；我仍然看到重复的东西:
I adjusted my views select code as per the above article but it didn't work for me; I still saw duplicates:
SELECT ROW_NUMBER() OVER (ORDER BY Car.Id) AS NID,
Car.Id,
Booking.BookingStartDate
... etc...
FROM Cars AS Car INNER JOIN
Booking AS Booking ON Car.Id = Booking.Car_Id
推荐答案
除了上述[Key]中的视图之外，我还进行了一些挖掘工作，发现其他线程指向 .AsNoTracking()作为潜在的解决方案.我对此进行了更多研究，并尝试在我的解决方案中实现它.
I did some more digging and aside from the above-mentioned [Key] for views, other threads I found pointed at .AsNoTracking() as a potential solution. I investigated this a little more and attempted to implement this on my solution.
以下是与我的问题有关的评论之一:
Here is one of those comments relating to my problem:
AsNoTracking()允许每条记录唯一的密钥".EF中的要求被忽略(其他答案未明确提及).
AsNoTracking() allows the "unique key per record" requirement in EF to be bypassed (not mentioned explicitly by other answers).
这在读取不支持唯一键的视图时非常有用，因为某些字段可能为空，或者该视图的性质在逻辑上不可索引.
This is extremely helpful when reading a View that does not support a unique key because perhaps some fields are nullable or the nature of the view is not logically indexable.
对于这些情况，键"可以设置为任何不可为空的列，但随后每个查询都必须使用AsNoTracking()，否则将跳过记录(按键重复)
For these cases the "key" can be set to any non-nullable column but then AsNoTracking() must be used with every query else records (duplicate by key) will be skipped
在我的 GenericRepository.cs 中，我在 Get 方法上设置了此值，并且网格上的结果现在准确无误.
Within my GenericRepository.cs I set this value on the Get method and the results on my grid are now accurate without any duplication going on.
这是我更改的代码:
GenericRepository.cs
public virtual IEnumerable<TEntity> Get(
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
string includeProperties = "")
{
IQueryable<TEntity> query = dbSet.AsNoTracking();
if (filter != null)
{
query = query.Where(filter);
}
foreach (var includeProperty in includeProperties.Split
(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
query = query.Include(includeProperty);
}
if (orderBy != null)
{
return orderBy(query).ToList();
}
else
{
return query.ToList();
}
}
此更改解决了我的问题.希望以后不会对此产生不良影响:)感谢所有花时间回复的人.
This change as solved my problem. Hopefully, there will no unwanted effects from this later down the line :) thanks to everyone who took the time to reply.
这篇关于为什么我的EF似乎从正在运行的SQL视图中返回重复的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
