CODEWITHSUNDEEP
c#sqlsql-serverlinq
Tartar
Tartar

Reputation: 5452

Writing sql query in linq syntax

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;

Upvotes: 0

Views: 62

Answers (1)

Eric Scherrer
Eric Scherrer

Reputation: 3408

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)

Upvotes: 1

Related Questions