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

You may have to tweak this slightly as I was going off the cuff, but there are a couple of major things to keep in mind. If you have your relationships set up properly in your dbml, you should be able to do inner joins implicitly and just access the data through your initial table. Also, left joins in LINQ are not as straight forward as we may hope and you have to go through the DefaultIfEmpty syntax in order to make it happen. I created an anonymous type here, but you may want to put into a DTO class or something to that effect. I also didn't know what you wanted to do in the case of nulls, but you can use the ?? syntax to define a value to give the variable if the value is null. Let me know if you have additional questions...

var query = (from a in context.Appointment
join b in context.AppointmentFormula on a.AppointmentId equals b.AppointmentId into temp
from c in temp.DefaultIfEmpty()
join d in context.AppointmentForm on a.AppointmentID equals e.AppointmentID into temp2
from e in temp2.DefaultIfEmpty()
where a.RowStatus == 1 && c.RowStatus == 1 && a.Type == 1
select new {a.AppointmentId, a.Status, a.Type, a.Title, c.Days ?? 0, a.Type.Description, e.FormID ?? 0}).OrderBy(a.Type);

Answer 2

If you want to preserve the (NOLOCK) hints, I have blogged a handy solution using extension methods in C#. Note that this is the same as adding nolock hints to every table in the query.

Answer 3

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 }