Some help with SQL to Linq Conversions

jkirkerx

I'm trying to convert this to Linq. I didn't see a OR in Linq, so I'm figuring you can do multiple Where stateements. And on the date, I'm not sure if I should calculate it in code and jsut submit the date, or if there is a way to use Linq to do Date Calcs.

" SELECT COUNT(*) " & _
" FROM CRM_MESSAGES " & _
" WHERE " & _
" MessageStatus='MESSAGE_COMPLETE' " & _
" OR MessageStatus='MESSAGE_PENDING' " & _
" OR MessageStatus='MESSAGE_FORWARD' " & _
" AND ContactDate >=DATEADD(day, -30, getdate()) "

So Far I've got this

Dim messages As IQueryable(Of CRM_MESSAGES)
messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_COMPLETE")
messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_PENDING")
messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_FORWARD")
messages = messages.Where(Function(m) m.ContactDate >= m.ContactDate.Date.AddDays(-30))

Richard Deeming

Multiple Where statements is equivalent to an AND operator. For an Or operator, use VB's OrElse operator:

messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_COMPLETE" OrElse m.MessageStatus = "MESSAGE_PENDING" OrElse m.MessageStatus = "MESSAGE_FORWARD")

Your date comparison won't do anything - you're asking for all messages where the contact date is later than a date 30 days before the contact date, which is all of them. Use Date.Today.AddDays(-30) instead:

messages = messages.Where(Function(m) m.ContactDate >= Date.Today.AddDays(-30))

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

I would of never figured that out on the multiple where statements. And the Date bombed, so I tried DateTime.Now.AddDays(-30), but changed to your example before testing it. I wonder if this will now work without using IQueryable? I'll give it a spin. It didn't work LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression. I'll go back to IQueryable and give it a try.

Dim pValue As Integer = 0
Using context As New CRMContext()

        pValue = \_
            (From item In context.CRM\_MESSAGES
             Where item.MessageStatus = "MESSAGE\_COMPLETE" \_
             OrElse item.MessageStatus = "MESSAGE\_PENDING" \_
             OrElse item.MessageStatus = "MESSAGE\_FORWARD" \_
             And item.ContactDate >= Date.Today.AddDays(-30)).Count()

    End Using

    Return pValue

jkirkerx

DBFunctions for EF 6.0, Thanks!

Imports System.Data.Entity

Dim pValue As Integer = 0

    Using context As New CRMContext()

        pValue = \_
            (From item In context.CRM\_MESSAGES
             Where item.MessageStatus = "MESSAGE\_COMPLETE" \_
             OrElse item.MessageStatus = "MESSAGE\_PENDING" \_
             OrElse item.MessageStatus = "MESSAGE\_FORWARD" \_
             And item.ContactDate >= DbFunctions.AddDays(DateTime.Now(), -30)).Count()

    End Using

    Return pValue

Richard Deeming

jkirkerx wrote:

LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.

You might need to move the date outside of the filter:

Dim minDate As Date = Date.Today.AddDays(-30)
messages = messages.Where(Function(m) m.ContactDate >= minDate)

Alternatively, use the EntityFramework.SqlServer assembly, and the System.Data.Entity.SqlServer.SqlFunctions class[^]:

messages = messages.Where(Function(m) m.ContactDate >= SqlFunctions.DateAdd("day", -30, SqlFunctions.GetDate()))

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer