LINQ nested query issue - System.NotSupportedException

Question

I'm trying to reproduce the following SQL query in LINQ:

select *
  from dbo.events_log 
 where std_datetimeseq < '2014011523:49:00001'
   and logname in ('S', 'D', 'O')
   and std_datetimeseq = (
       select min(std_datetimeseq)
         from dbo.events_log
        where std_datetimeseq < '2014011523:49:00001'
          and logname in ('S', 'D', 'O'))

This is what I have:

from e in _context.EventLogs
                    where e.std_datetimeseq.CompareTo(stdDateTimeSeq) < 0
                      && eventMessage.Content.EquipToSearch.Contains(e.logname)
                      && e.std_datetimeseq.CompareTo(
                      ((from e2 in _context.EventLogs
                        where e2.std_datetimeseq.CompareTo(stdDateTimeSeq) < 0
                            && eventMessage.Content.EquipToSearch.Contains(e2.logname)
                        select e2).Min().std_datetimeseq)) == 0
                           select e

I keep getting this exception:

{"The specified method 'MyEntityModel.EventLog Min[EventLog](System.Linq.IQueryable`1[HDSREntityDataModelNS.EventLog])' 
on the type 'System.Linq.Queryable' cannot be translated into 
a LINQ to Entities store expression because no overload matches the passed arguments."}

I have no idea what's wrong.

eventMessage.Content.EquipToSearch is a List with "S", "D", "O" in it. The LINQ is going against entity framework.

Thanks

First try changing select e2).Min().std_datetimeseq)) == 0 to select e2.std_datetimeseq).Min())) == 0. — juharr
– juharr, Commented Jan 30, 2015 at 19:44
Rather than using CompareTo to compare the dates, just use the less than and equality operators. — Servy
– Servy, Commented Jan 30, 2015 at 19:46
Also, as a general rule whenever you have an error like this, try cutting pieces of the query bit by bit to try to find the smallest query that still produces the error; rather than just trying to guess at what's broken, and thus what needs to be re-written. — Servy
– Servy, Commented Jan 30, 2015 at 19:54
It was as easy as changing select e2).Min().std_datetimeseq)) == 0 to e2.std_datetimeseq).Min())) == 0. Thanks for both of your suggestions. — KrisW
– KrisW, Commented Jan 30, 2015 at 19:54
@juharr You should post that as the answer; apparently that was the only problem. — Servy
– Servy, Commented Jan 30, 2015 at 20:09

juharr · Accepted Answer · 2015-01-30 20:33:49Z

1

The problem is with the sub query

(from e2 in _context.EventLogs
 where e2.std_datetimeseq.CompareTo(stdDateTimeSeq) < 0
    && eventMessage.Content.EquipToSearch.Contains(e2.logname)
 select e2).Min().std_datetimeseq

In order to get the min std_datetimeseq value you have to select it, otherwise you are trying to find the minimum EventLogs item which not only doesn't make sense, but cannot be translated to SQL by EF. Instead do the following.

(from e2 in _context.EventLogs
 where e2.std_datetimeseq < stdDateTimeSeq
    && eventMessage.Content.EquipToSearch.Contains(e2.logname)
 select e2.std_datetimese).Min()

On a side note your query will be easier to read if you just use the comparison operators instead of CompareTo as suggested by Servy.

answered Jan 30, 2015 at 20:33

juharr

32.4k4 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LINQ nested query issue - System.NotSupportedException

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related