CODEWITHSUNDEEP
c#linqdynamics-crm
LoneWolf
LoneWolf

Reputation: 53

Date range filter in linq to crm

I am using LINQ to query CRM entity. Below is my code

var data = svcContext.CreateQuery("myentity");
 if (info.FromDate != null && info.ToDate != null)
 {
    data = data.Where(r => r.GetAttributeValue<DateTime>("rundate") >= info.FromDate.Value.Date && r.GetAttributeValue<DateTime>("rundate") <= info.ToDate.Value.Date);
 }

This returns all records between FromDate and ToDate but it also consider time part while comparing which return wrong records. I want to truncate time part and use only date part for comparing. I already used code like this

1.

 if (info.FromDate != null && info.ToDate != null)
 {
    data = data.Where(r => r.GetAttributeValue<DateTime>("rundate").Date >= info.FromDate.Value.Date && r.GetAttributeValue<DateTime>("rundate").Date <= info.ToDate.Value.Date);
 }

2.

 if (info.FromDate != null && info.ToDate != null)
 {
    data = data.Where(r => Convert.ToDateTime(r.Attributes["rundate"]).Date >= info.FromDate.Value.Date && Convert.ToDateTime(r.Attributes["rundate"]).Date <= info.ToDate.Value.Date);
 }

3.

 if (info.FromDate != null && info.ToDate != null)
 {
    data = data.Where(r => EntityFunctions.TruncateTime(r.GetAttributeValue<DateTime>("rundate")) >= info.FromDate.Value.Date && EntityFunctions.TruncateTime(r.GetAttributeValue<DateTime>("rundate")).Date <= info.ToDate.Value.Date);
 }

These all returns "Invalid 'where' condition. An entity member is invoking an invalid property or method." error. So how to compare by only date part and truncate time part. Thank you..

Upvotes: 0

Views: 1325

Answers (2)

J. Doe
J. Doe

Reputation: 216

If the actual times do not matter, why don't you query your records by

var data = svcContext.CreateQuery("myentity");
if (info.FromDate != null && info.ToDate != null)
{
    var compareFromDate = info.FromDate.Value.Date;
    var compareToDate = info.ToDate.Value.Date.AddDays(1);
    data = data.Where(r => r.GetAttributeValue<DateTime>("rundate") >= compareFromDate && r.GetAttributeValue<DateTime>("rundate") < compareToDate);
}

Upvotes: 1

Aron
Aron

Reputation: 3935

The CRM LINQ provider has some limitations that LINQ to Objects does not.

Adding ToList() to the CreateQuery result converts to using LINQ to Objects, which should allow you to do what you want.

For the comparisons you're looking to do, coverting to OLE Automation's decimal date format (OADate), then casting to int gives you a serial number for the date without any time component.

And, for clarity I moved the predicate to a separate method.

public override void Run()
{
    using (var context = new Microsoft.Xrm.Sdk.Client.OrganizationServiceContext(svc))
    {
        var result = (from e in context.CreateQuery("account").ToList()
                        .Where(ne => isBetween(ne.GetAttributeValue<DateTime>("createdon"), DateTime.MinValue, DateTime.Now))
                        select e).ToList();     
    }
}

private bool isBetween(DateTime value, DateTime min, DateTime max)
{
    var val = (int)value.ToOADate();
    return val >= (int)min.ToOADate() || val <= (int)max.ToOADate();
}

Upvotes: 0

Related Questions