How to make EF.net filter all the data on SQL Server and not on C#

.net-core c# entity-framework-core sql-server

Question

I was doing some tests using Entity Framework .Net core. I turned on the SQL server profile and I started to compare my query and what EF executes on SQL Server.

Here is my modal class:

public class Offer : BaseModel
    {
        public DateTime HostStartDate { get; set; }
        public DateTime HostEndDate { get; set; }
        public int Guests { get; set; }
        public decimal AmountOffer { get; set; }
        public OfferStatus Status { get; set; }
        public PaymentStatus PaymentStatus { get; set; }

        public long CityId { get; set; }
        public virtual City City { get; set; }

        [Required]
        public string UserId { get; set; }
        public virtual ApplicationUser User { get; set; }

        public virtual ICollection<Bid> Bids { get; set; }
        public bool IsDeleted { get; set; }

        [NotMapped]
        public bool IsVisible => HostStartDate.Date >= DateTime.Today.Date;



        public void SetSoftDelete()
        {
            IsDeleted = true;
            Bids.ToList().ForEach(n => n.SetSoftDelete());
        }
    }

 public class Bid : BaseModel , IStateAware
    {
        [NotMapped]
        public override long Id { get; set; }

        public long OfferId { get; set; }
        public virtual Offer Offer { get; set; }

        public long PropertyId { get; set; }
        public virtual Property Property { get; set; }

        public DateTime? Accepted { get; set; }
        public bool Read { get; set; }
        public bool IsDeleted { get; set; }

        [NotMapped]
        public ModelState State { get; set; }

        [NotMapped]
        public bool IsPayed => Accepted.HasValue;

        [NotMapped]
        public bool IsAccepted => Accepted.IsNotNull();

        public void SetSoftDelete()
        {
            IsDeleted = true;
            State = ModelState.Modified;
        }
    }

I have a generic class to do my Find, like this:

    public IQueryable<TEntity> FindBy(
        Expression<Func<TEntity, bool>> filter = null,
        Expression<Func<TEntity, object>> includeProperty = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null)
    {
        IQueryable<TEntity> query = context.Set<TEntity>();

        if (filter.IsNotNull())
        {
            query = query.Where(filter);
        }

        if (includeProperty.IsNotNull()) 
        {
            query = query.Include(includeProperty);
        }

        if (orderBy.IsNotNull())
        {
            query = orderBy(query);
        }

        return query.AsNoTracking();
    }

I call it like this:

var finalEnd = new DateTime(end.Year, end.Month, end.Day, 23, 59, 59);
var data = await _bidRepo.FindBy(n => !n.OfferId.Equals(offerId)
                                            && n.PropertyId.Equals(propertyId)
                                            && !n.IsDeleted
                                            && !n.IsAccepted
                                            && !n.IsPayed
                                            && !n.IsInvalidate
                                            && ((n.Offer.HostStartDate.Date >= start.Date && n.Offer.HostStartDate.Date <= end.Date) ||
                                            (n.Offer.HostEndDate > finalEnd && n.Offer.HostEndDate.Date <= end.Date))
                                            ).ToListAsync();

My query returns 4 items, and it is fine. But, on SQL server Profile my query are not take care of any thing different of the dates.

This is the profile:

exec sp_executesql N'SELECT [n].[OfferId], [n].[PropertyId], [n].[Accepted], [n].[CreatedBy], [n].[CreatedDate], [n].[IsDeleted], [n].[IsInvalidate], [n].[ModifiedBy], [n].[ModifiedDate], [n].[Read]
FROM [Bids] AS [n]
INNER JOIN [Offers] AS [n.Offer] ON [n].[OfferId] = [n.Offer].[Id]
WHERE ((CONVERT(date, [n.Offer].[HostStartDate]) >= @__start_Date_2) AND (CONVERT(date, [n.Offer].[HostStartDate]) <= @__end_Date_3)) OR (([n.Offer].[HostEndDate] > @__finalEnd_4) AND (CONVERT(date, [n.Offer].[HostEndDate]) <= @__end_Date_3))',N'@__start_Date_2 datetime2(7),@__end_Date_3 datetime2(7),@__finalEnd_4 datetime2(7)',@__start_Date_2='2020-02-23 00:00:00',@__end_Date_3='2020-02-25 00:00:00',@__finalEnd_4='2020-02-25 23:59:59'

On this SQL server statement, I can't see any of boolean filters or Ids.

How can I make sure all clauses are executed on SQL server and not bringing part of the date to C# and later removing it?

1
1
2/20/2020 10:13:18 PM

Popular Answer

There are some EF Core implementation quirks here, but the main issue is that you are using not mapped / get only properties which cannot be translated to SQL. EF Core 3.0+ will simply throw runtime exception, but EF Core 1.x / 2.x will evaluate such conditions in memory after retrieving the data from the database.

Why they cannot be translated? Because EF Core is not a compiler and all it sees is something like

public bool IsPayed { get; }

In order to be translatable, EF Core must "see" the implementation inside the query expression tree. Which means that basically you can't use such properties (and also custom methods like your IsNotNull) in LINQ to Entities query and should code them directly eg. instead of

&& !n.IsPayed

you should use

&& !n.Accepted.HasValue

Similar for && !n.IsAccepted (and most likely !n.IsInvalidate).

Of course this is code duplication and less readable, but this is a general problem with IQueryable expression trees which has no compiler or BCL solution. There are some 3rd party libraries which try to address it. For instance, Lambda Injection from NeinLinq. If you insist using OOP in your data model / queries, consider using some of these 3rd party libraries because you won't get it form C# / BCL / EF Core.

1
2/21/2020 2:58:50 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow