LINQ syntax for SQL INNER JOINS

Question

I'm a complete novice in linq.

I have a T-SQL statement that I don't know write in Linq.

The database tables structure looks like this: DB structure

The SQL code looks like this:

SELECT 
    e.PersonId, e.PhoneNoId, e.PhoneId, s.StateId, e.AssignDate, e.DiscardDate, e.Note
FROM
    dbo.Evidence AS e 
INNER JOIN
    dbo.Phone AS p ON e.PhoneId = p.PhoneId 
INNER JOIN
    dbo.[State] AS s ON p.StateId = s.StateId

I'd like to know how to write this code in linq with lambda expression.

Thanks for help.

Do you want to rewrite your `T-SQL` query in `EntityFramework` and `C#`? — Iliar Turdushev, Apr 21 '20 at 10:21
Yes, please. I want to rewrite `T-SQL` to `LINQ` syntax. Will be used into `ADO.NET` project written in `C#`. Thank you. — Rado, Apr 21 '20 at 16:30

Iliar Turdushev · Accepted Answer · 2020-04-21T17:49:52.977

You can write you T-SQL query in LINQ using

either query expression syntax:

// Declare query.
var query =
    from e in db.Evidence
    join p in db.Phone on e.PhoneId equals p.PhoneId
    join s in db.State on p.StateId equals s.StateId
    select new {e.PersonId, e.PhoneNoId, e.PhoneId, s.StateId, e.AssignDate, e.DiscardDate, e.Note};
// Execute query.
var result = query.ToList();

or method-based query syntax:

// Declare and execute query.
var result =
    db.Evidence
       .Join(db.Phone, e => e.PhoneId, p => p.PhoneId, (e, p) => new {e, p})
       .Join(db.State, t => t.p.StateId, s => s.StateId, (t, s) =>
          new {t.e.PersonId, t.e.PhoneNoId, t.e.PhoneId, s.StateId, t.e.AssignDate, t.e.DiscardDate, t.e.Note});
       .ToList(); // Method "ToList()" executes query.

In this samples variable db is of type DbContext (I assume that you are going to use EntityFramework).

Refer next links for details:

This is exactly what I was looking for. Thank you IIiar. – Rado Apr 21 '20 at 20:41 — Rado, Apr 21 '20 at 20:41

LINQ syntax for SQL INNER JOINS

1 Answers1