Why does my EF appear to return duplicate rows from my SQL View which is working?

Question

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.

Table: Cars

• [Id]
• [Registration]
• [Make]
• [Model]

Table: Bookings

• [Id]
• [BookingStartDate]
• [BookingEndDate]
• [CarId]

View: 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

If I run the query in SSMS I get all the expected results, for example:

• Car 1, Booked 12/03/2018
• Car 1, Booked 19/09/2018

When I access the same view from my MVC Application I get:

• Car 1, Booked 12/03/2018
• Car 1, Booked 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.

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);
}

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;
     }
 }

Entity Class

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; }
    }
}

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).

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

Answer 1

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() 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.

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

Source: What difference does .AsNoTracking() make?

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.

Here is the code I changed:

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.

Answer 2

As you've mentioned before views can get messed if there's no primary key.

Typically you need to add a [Key] attribute to at least one of the properties on your view - the original recommendation back in EF 1 with the edmx designer was to just set ALL the properties on the view to be primary keys but this may be overkill. But if adding it to one property doesn't work try adding it to all of them or a subset of them so that each entity has a unique key combo e.g.

public partial class CarsBookings
{
    [Key]
    public int Id { get; set; }

    [Key]
    public string Registration { get; set; }
}