Writing sql query in linq syntax

Question

I'am trying to write the following query in linq syntax but i can make it. I'am getting errors at the marked line Error The best overloaded method match for 'System.Collections.Generic.List<AnonymousType#1>.Contains(AnonymousType#1)' has some invalid arguments. How can i write it properly ?

sql:

     select * from [dbo].[TableOrder]
     where TableID not in (
     select tbl.TableID from [dbo].[TableOrder] tbl
     left join [dbo].[Restorant] r on tbl.RestorantID = r.RestorantID 
     left join [dbo].[Reservation] res on res.TableID = tbl.TableID
     where ReservationDate = '15.06.2014' and ResStartTime = '2100' and ResEndTime='2299')

linq:

            var query = (from t in db.TableOrderTbls.AsQueryable()
                     join r in db.RestorantTbls on t.RestorantID equals r.RestorantID
                     join res in db.ReservationTbls on t.TableID equals res.TableID
                     where (resv.RseservationDate == res.ReservationDate && resv.RestorantName == r.RestorantName) && ((Convert.ToInt32(resv.ResStartTime) < Convert.ToInt32(res.ResStartTime) &&Convert.ToInt32(resv.ResEndTime) < Convert.ToInt32(res.ResStartTime))||((Convert.ToInt32(resv.ResStartTime) > Convert.ToInt32(res.ResEndTime) && Convert.ToInt32(resv.ResEndTime) > Convert.ToInt32(res.ResEndTime))))
                     select new {r.RestorantName, r.RestorantID, t.TableID, t.TableType}).ToList();

        var q = from t in db.TableOrderTbls.AsQueryable()
                where query.Contains(t.TableID) // ERROR LINE
                select t;

Why not make the lives of those who'd like to help you by including the full error details in your question? — Daniel Kelley
– Daniel Kelley, Commented May 30, 2014 at 14:23
Now it makes sense. Your select is returning a new anonymous type, containing 4 properties. You are treating it as if it contains just 1 in your call to query.Contains(t.TableID). Using your code as-is, you would need to pass your anonymous type to the Contains method. — Daniel Kelley
– Daniel Kelley, Commented May 30, 2014 at 14:29

gunr2171 · Accepted Answer · 2014-05-30 14:32:11Z

1

Your query object is a list of:

select new {r.RestorantName, r.RestorantID, t.TableID, t.TableType}

But you are trying to see if it contains a:

TableOrderTbls.TableId

An anonymous {r.RestorantName, r.RestorantID, t.TableID, t.TableType} is not the same type as an TableOrderTbls.TableId.

You need to be more specific in mapping your anonymous type to TableOrderTbls.TableId.

Wild guess here, try this:

where query.Any(x => x.TableID == t.TableID)

edited May 30, 2014 at 14:32

gunr2171

17.7k26 gold badges68 silver badges102 bronze badges

answered May 30, 2014 at 14:30

Eric Scherrer

3,4091 gold badge21 silver badges35 bronze badges

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

6 Comments

Eric Scherrer Over a year ago

So it's compiling, running and not returning an error. Are you sure the data is good and it should be returning something? Put a breakpoint and inspect the results of query, then also break out the second query without the where, ToList() it and inspect. Make sure it even matches.

Tartar Over a year ago

it is returning NotSupportedException

Eric Scherrer Over a year ago

So initially when you tried my answer it returned null, but now it is returning a not supported exception? What changed?

Tartar Over a year ago

I don't know, I'am just getting NotSupportedException and Any() function cause this.

martinoss Over a year ago

Use Eric's code and remove .ToList() from the first query.

|

Collectives™ on Stack Overflow

Writing sql query in linq syntax

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related