How to convert the SQL to LINQ query

Question

This is the SQL query I have

Select NAME from RM_ROOM where RM_ROOM.ROOMID IN (select  DEST_ROOMID from  RM_ROOM, RM_APPROVED_ROOM_STATE,RM_ENTITY_TYPE where RM_ROOM.ROOMID = RM_APPROVED_ROOM_STATE.SOURCE_ROOMID and RM_ENTITY_TYPE.ENTITY_TYPEID = RM_APPROVED_ROOM_STATE.ENTITY_TYPEID and RM_ROOM.NAME = 'A1 RAF' and RM_ENTITY_TYPE.ENTITY_NAME = 'MICE');

This is the LINQ query i have tried

var rooms = from s in db.RM_APPROVED_ROOM_STATE
               join t in db.RM_ROOM
                   join a in db.RM_ENTITY_TYPE
                     on s.ENTITY_TYPEID equals a.ENTITY_TYPEID
                   select news.DESTID

I am new to LINQ please help me out

---

Samson Yerraguntla

Answer 1

I am new to LINQ please help me out

https://www.linqpad.net/

Answer 2

Hi,

your query could be

from room in RM_ROOM join DestRoom in db.RM_ROOM  on DestRoom.DEST_ROOMID equals room.ROOMID join RS in db.RM_APPROVED_ROOM_STATE on DestRoom.ROOMID equals RS.SOURCE_ROOMID   join ET in db.RM_ENTITY_TYPE on ET.ENTITY_TYPEID = RS.ENTITY_TYPEID where DestRoom.Name =="A1 RAF" && ET.ENTITY_NAME == "MICE" select room.Name

However it's better to have your queries especially if they are complex in SQL using stored procedures for better performance.

using Linq, it's highly recommended to use compiled linq queries, 

please check https://msdn.microsoft.com/en-us/library/bb896297%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

---

Thanks, Mahmoud

Answer 3

Hi SamsonYerraguntla,
Thank you for posting here.
The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.
Since your issue is about Linq To SQL, I will move your thread to that forum for better support. If you have any problem, please feel free to contact us.
Best Regards,
Hart

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey.

Answer 4

Hi SamsonYerraguntla,

>>How to convert the SQL to LINQ query

Based on your SQL statement, it seems that you use in clause, we could use contains method to achieve it, and I create a simple demo as below for your reference.

//retrieve id collection from related table.
var rIds = db.RM_ROOMs
                    .Join(db.RM_APPROVED_ROOM_STATEs, r => r.ROOMID, a => a.SOURCE_ROOMID, (r, a) => new { r, a })
                    .Join(db.RM_ENTITY_TYPEs, ra => ra.a.ENTITY_TYPEID, e => e.ENTITY_TYPEID, (ra, e) => new { ra, e })
                    .Select(m => m.ra.a.DEST_ROOMID);
//use contain method retrieving related records.
                var result = db.RM_ROOMs.Where(s => rIds.Contains(s.ROOMID)).Select(r=>r.NAME);

Best regards,

Cole Wu

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.