Convert SQL query to LINQ method syntax in C#

Question

I want to get data from 4 tables that have a relationship with each other.

scenario: A person can be loan a device that belongs to a certain program

      Database Tables
person          -> Persons Database Table           
device loan     -> Loan Database Table  
device          -> Devices Database Table  
program         -> Programs Database Table

These are the database tables in a diagram:

This is the desired output:

The output above was produced by the following SQL query :

SELECT A.*, 
COALESCE(ps.first_name,'') AS firstname,
COALESCE(ps.last_name,'') AS lastname,
COALESCE(p.program_name, '') AS program_Name 
FROM devices A 
LEFT JOIN device_loans l ON l.device_id = A.device_id
LEFT JOIN persons ps ON ps.person_id = l.person_id
LEFT JOIN programs p ON A.program_id = p.program_id
WHERE A.device_type ="Tablet" AND  p.program_id = 5;

I want to Convert the SQL query to LINQ method syntax. I have try the folloing method :

var devices = from ds in _context.Devices
        join p in _context.Programs on ds.ProgramID equals p.Id 
        join l in _context.DeviceLoan on ds.Id equals l.device_id 
        join ps in _context.Persons on l.device_id equals ps.Id 
        where ds.Type == typeName
        where ds.ProgramID == id
        select new Device
              {
                  Id = ds.Id,
                  ProgramID = p.Id,
                  Type = ds.Type,
                  Serial_Number = ds.Serial_Number,
                  Label = ds.Label,
                  Price = ds.Price,
                  Purchase_Date = ds.Purchase_Date,
                  Device_Status = ds.Device_Status
                  
              };

What I want is to create a LINQ method Syntax that include the Firstname and Lastname from the Database Persons Table, Database Programs Table and data from Database Devices Table just like the SQL query above.

Output: I want all devices of a certain program, loan or not loan with 
        first name & last name of a Person loan too.

Thank you for Your help

I think that your problem is that you are not doing a left join, to see how to do it: https://stackoverflow.com/questions/3404975/left-outer-join-in-linq — Iria, May 13 '21 at 19:12
Does this answer your question? [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Iria, May 13 '21 at 19:13
Use linqpad for converting sql to linq and vice versa. This is just a application just install and you can write queries. https://www.linqpad.net/ — Rahul Shukla, May 13 '21 at 19:16
@RahulShukla I don't think LINQPad goes from sql to Linq. It will do Linq to sql. — Crowcoder, May 13 '21 at 19:23
If you post classes, i’ll help you to convert this to LINQ. — Svyatoslav Danyliv, May 14 '21 at 06:17

score 0 · Answer 1 · answered Jun 01 '21 at 14:22

Thanks for the help guys. I created the following query and it gives me what I wanted.

var devices = _context.Devices.
                    Where(c => c.Type == typeName && c.ProgramID == id).
                    Include(c => c.Program).
                    Include(c => c.DeviceLoans)
                        .ThenInclude(x => x.Persons).
                    ToList();

in the .cshtml

var loan_object = item.DeviceLoans.Where(d=>d.device_id==item.Id).FirstOrDefault<Device_Loan>()

To get the name of the person that the device is a loan to, I use the above line of code.

Convert SQL query to LINQ method syntax in C#

1 Answers1