JOIN and LEFT JOIN equivalent in LINQ

Question

I am working with the following SQL query:

SELECT 
a.AppointmentId,
a.Status,
a.Type,
a.Title,
b.Days,
d.Description,
e.FormId
FROM Appointment a (nolock)
LEFT JOIN AppointmentFormula b (nolock)
ON a.AppointmentId = b.AppointmentId and b.RowStatus = 1
JOIN Type d (nolock)
ON a.Type = d.TypeId
LEFT JOIN AppointmentForm e (nolock)
ON e.AppointmentId = a.AppointmentId
WHERE a.RowStatus = 1
AND a.Type = 1
ORDER BY a.Type

I am unsure how to achieve the JOINs in LINQ. All my tables have foreign key relationships.

Answer 1

SELECT A.X, B.Y
FROM A JOIN B ON A.X = B.Y

This linq method call (to Join) will generate the above Join.

var query = A.Join
(
  B,
  a => a.x,
  b => b.y,
  (a, b) => new {a.x, b.y} //if you want more columns - add them here.
);

---

SELECT A.X, B.Y
FROM A LEFT JOIN B ON A.X = B.Y

These linq method calls (to GroupJoin, SelectMany, DefaultIfEmpty) will produce the above Left Join

var query = A.GroupJoin
(
  B,
  a => a.x,
  b => b.y,
  (a, g) => new {a, g}
).SelectMany
(
  z => z.g.DefaultIfEmpty(),
  (z, b) =>
    new  { x = z.a.x, y = b.y } //if you want more columns - add them here.
);

The key concept here is that Linq's methods produce hierarchically shaped results, not flattened row-column shapes.

• Linq's GroupBy produces results shaped in a hierarchy with a grouping key matched to a collection of elements (which may not be empty). SQL's GroupBy clause produces a grouping key with aggregated values - there is no sub-collection to work with.
• Similarly, Linq's GroupJoin produces a hierarchical shape - a parent record matched to a collection of child records (which may be empty). Sql's LEFT JOIN produces a parent record matched to each child record, or a null child record if there are no other matches. To get to Sql's shape from Linq's shape, one must unpack the collection of child records with SelectMany - and deal with empty collections of child records using DefaultIfEmpty.

---

And here's my attempt at linquifying that sql in the question:

var query =
  from a in Appointment
  where a.RowStatus == 1
  where a.Type == 1
  from b in a.AppointmentFormula.Where(af => af.RowStatus == 1).DefaultIfEmpty()
  from d in a.TypeRecord //a has a type column and is related to a table named type, disambiguate the names
  from e in a.AppointmentForm.DefaultIfEmpty()
  order by a.Type
  select new { a.AppointmentId, a.Status, a.Type, a.Title, b.Days, d.Description, e.Form }