0

I have an Entity object with 2 tables: rest and rest_services

I want to replicate the following SQL query using a chain LINQ query (and put this in a decimal List)

SELECT a.rest_id
FROM rest a, rest_services b
WHERE a.rest_id = b.rest_id
AND a.manager_id = 500
AND b.channel = 5

I have gone through various stackoverflow questions

Join/Where with LINQ and Lambda

How to perform Join between multiple tables in LINQ lambda

I had minor success with the non chain syntax but I cannot put the second part of the WHERE correctly

 var Hot =
                from h in db.rest
                join hs in db.rest_services on h.rest_id equals hs.rest_id
where h.manager_id == 523 && hs.channel== 5
                select h.rest_id

Any help appreciated

PanosPlat
  • 940
  • 1
  • 11
  • 29
  • 3
    [Join](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.join) + [Where](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.where) + [Select](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.select). Give it a shot and then [edit] your question and share the code you have. – Igor Jun 09 '20 at 19:52
  • thanx mate. Done – PanosPlat Jun 09 '20 at 19:58
  • 1
    `where h.manager_id == 523 && x => hs.channel== 5` ← put that after the `join` and remove the `.Where(...` completely. That should be it unless you do not want projection for your output in which case `select h.rest_id` instead of the `select` you have now. – Igor Jun 09 '20 at 19:59
  • Thanx. I edited the Question. Unfortunately I get errors. – PanosPlat Jun 09 '20 at 20:06
  • 1
    That is my fault for not actually looking at the code I copied from your attempt: Fixed: `where h.manager_id == 523 && hs.channel== 5 ` – Igor Jun 09 '20 at 20:07
  • Thank you! How can I put this into a decimal List? – PanosPlat Jun 09 '20 at 20:09
  • 1
    `var myListOfDecimals = Hot.ToList();` – Igor Jun 09 '20 at 20:09

1 Answers1

1

Since you're only interested in outputting data from 'rest', the more performant version of the SQL you're after should be:

select  a.rest_id
from    rest a
where   a.manager_id = 500 
and     exists (
            select  0
            from    rest_services b
            where   a.rest_id = b.rest_id
            and     b.channel = 5
        )

And, using 'chain' syntax and outputting a decimal list, I believe this is translates to:

db.rest.Where(r => 
    r.manager_id == 500 && 
    db.rest_services.Any(
        rs => rs.channel == 5 && r.rest_id == rs.rest_id
    )
).Select(r => Convert.ToDecimal(r.rest_id))
.ToList();
pwilcox
  • 5,542
  • 1
  • 19
  • 31