CODEWITHSUNDEEP
c#entity-frameworkentity-framework-4.1entity-functions
Eran Betzalel
Eran Betzalel

Reputation: 4203

How to work around Entity Framework date time type bug?

I want to get the all the monthly expired products, here is the query for that:

_customerProductsRepository
    .Where(
        d =>
        !d.ReleaseDate.HasValue &&
        EntityFunctions.AddMonths(d.RenewalDate ?? d.AcquireDate, 1) < now)
    .ToArray();

AcquireDate is the first purchase of the product and RenewalDate is the last renewal of the product.

For some reason it translates to this SQL:

SELECT
[Extent1].[CustomerDidId] AS [CustomerDidId],
[Extent1].[DidNumber] AS [DidNumber],
[Extent1].[CountryId] AS [CountryId],
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[AcquireDate] AS [AcquireDate],
[Extent1].[ReleaseDate] AS [ReleaseDate],
[Extent1].[RenewalDate] AS [RenewalDate],
[Extent1].[RenewalNotificationDate] AS [RenewalNotificationDate]
FROM [dbo].[CustomerDids] AS [Extent1]
WHERE ([Extent1].[ReleaseDate] IS NULL) AND ((DATEADD (month, 1, [Extent1].[Rene
walDate])) < @p__linq__0)

There should be a case statement referring to the '??' sign, instead - it completely removed AcquireDate column.

How can I walk-around it?

Upvotes: 1

Views: 935

Answers (3)

Eranga
Eranga

Reputation: 32447

You have configured the property RenewalDate as required. Hence EF will optimize the query by evaluating the result of the "if" condition.

Upvotes: 1

ΩmegaMan
ΩmegaMan

Reputation: 31686

One scenario if you can't get this to work as IQueryable, get all the data as an IEnumerable (call asEnumerable) and then filter out via the date methods you specify.

Upvotes: 0

Qorbani
Qorbani

Reputation: 5905

Have you tried to do this way:

_customerProductsRepository
    .Where(
        d =>
        !d.ReleaseDate.HasValue &&
        (d.RenewalDate.HasValue ? 
              EntityFunctions.AddMonths(d.RenewalDate.Value, 1) < now) :
              EntityFunctions.AddMonths(d.AcquireDate, 1) < now))
    .ToArray();

Upvotes: 0

Related Questions