Equivalent to JOIN and LEFT JOIN 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 not sure how to achieve JOINs in LINQ. All my tables have foreign key relationships.

Answer 1

You may need to adjust a bit when I'm going with the cuff, but there are a few important things to keep in mind. If you have correctly configured your relations in your dbml, you should be able to do internal connections implicitly and just access the data through your initial table. Also, the left joins in LINQ are not as straightforward as we can hope for, and you need to go through the DefaultIfEmpty syntax for this to happen. Here I created an anonymous type, but you might want to put it in a DTO class or something like that. I also did not know what you wanted to do in the case of zeros, but you can use the syntax to determine the value that the variable gives if the value is null. Let me know if you have further 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

 SELECT AX, BY FROM A JOIN B ON AX = BY 

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

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

---

 SELECT AX, BY FROM A LEFT JOIN B ON AX = BY 

These linq method calls (for GroupJoin, SelectMany, DefaultIfEmpty) will call the above Left Join

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

The key concept here is that Linq methods produce results in a hierarchical form, rather than flattened row column forms.

• Linq GroupBy creates results formed in a hierarchy with a grouping key mapped to a collection of elements (which may be non-empty). SQL GroupBy creates a grouping key with aggregated values - no, to work with a sub-collection.
• Similarly, Linq GroupJoin creates a hierarchical form — a parent record that maps to a collection of child records (which may be empty). Sql LEFT JOIN creates a parent record corresponding to each child record, or a zero child record if there are no other matches. To go to the Sql form from the Linq form, you need to unzip the collection of child records using SelectMany and process the empty collections of child records using DefaultIfEmpty .

---

And here is my attempt to eliminate this 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 } 

Answer 3

If you want to keep the hints (NOLOCK), I wrote a good solution using extension methods in C #. Note that this is the same as adding nolock hints to each table in the query.