Lambda expression for Entity Framework query

Question

I'm stuck trying to map this sql:

select dt.Id, dateadd(dd,datediff(dd,0,dt.CreatedAt),0) as Ct, DId, Amount
from dt, ad
where dt.ADId = ad.ADId and ad.Id = '13B29A01-8BF0-4EC9-80CA-089BA341E93D'
order by dateadd(dd,datediff(dd,0,dt.CreatedAt),0) desc, DId asc

Into an Entity Framework-compatible lambda query expression. I'd appreciate any help.

Answer 1

I think something like the code below should work.

Guid dtId = new Guid("13B29A01-8BF0-4EC9-80CA-089BA341E93D");
DateTime compareDt = new DateTime(...);

var q = from dt in dts
        where dt.id == dtId
        orderby dt.Ad.CreatedAt, Did
        select new
        {
            dt.Ad,
            (dt.CreatedAt - compareDt).Days,
            DId,
            Amount
        };

dtId has to be outside of the query, because the entity framework mapper doesn't understand constructors that take parameters, similar for the DateTime. I couldn't completely figure out the datediff/dateadd part, looks like you're determining the total amount of days since a given datetime, which is what I assumed.

It could be that the datetime subtraction followed by TimeSpan.Days doesn't work in the query. If that's the case, pull it outside like so:

var q = from dt in dts
        where dt.id == dtId
        orderby dt.Ad.CreatedAt, Did
        select new
        {
            dt.Ad,
            dt.CreatedAt,
            DId,
            Amount
        };

This creates an IEnumerable of objects that have a DateTime CreatedAt property. If you call .ToList() now, the query is executed, and you can calculate stuff that Entity Framework doesn't support. Again, if the first attempt worked, that's the best solution if the number of days since X is what you need.

from item in q.ToList()
select new
{
    dt.Ad
    (dt.CreatedAt - compareDt).Days,
    DId,
    Amount
};