I found out how to perform a left outer join using LINQ from Bilal Haidar's blog. My example shows how to perform a left join from Person to Person Address.
LINQ Query
var query = (from p in dc.GetTable<Person>()
join pa in dc.GetTable<PersonAddress>() on p.Id equals pa.PersonId into tempAddresses
from addresses in tempAddresses.DefaultIfEmpty()
select new { p.FirstName, p.LastName, addresses.State });
SQL Translation
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
SELECT [t0].[FirstName], [t0].[LastName], [t1].[State] AS [State]
FROM [dbo].[Person] AS [t0]
LEFT OUTER JOIN [dbo].[PersonAddress] AS [t1] ON [t0].[Id] = [t1].[PersonID]
Need a break?
Checkout my virtual Florida Keys vacation slide show I built using a site I created that allows programmers to automate scripts for Google Street View using the programming language Forth.
6 comments:
It seems to me that code:
var query = (from p in dc.GetTable Person()
join pa in dc.GetTable PersonAddress () on p.Id equals pa.PersonId into tempAddresses
from addresses in tempAddresses.DefaultIfEmpty()
select new { p.FirstName, p.LastName, addresses.State });
will not work properly - Exception of type 'System.NullReferenceException : Object reference not set to an instance of an object' will fire.
May be the best way is replace the part of code ', addresses.State' to next: ', addresses == null? string.Empty : addresses.State'
Alexey,
When I read your comment, I initially agreed that it looks like it would cause a null reference error. I went through and set up a test case, a person without an address, and the query performed just fine. The addresses.State method returned a null value. I wonder if the previous default if empty function is setting the values of address to null if there is no match between addresses and person. Therefore, addresses is initialized, but if there is no match, the properties within addresses is set to null.
Hi, how do you get sql translation in linq?
You can get access to the Sql translation by attaching a object the inherits from System.IO.TextWriter to the Log property on the DataContext class.
One example is to use Console.Out. Another is to use a simple class the writes translations to the Output window in Visual Studio.
This is an example of it:
using System.Text;
using System.IO;
using System.Diagnostics;
public class DebugWriter : TextWriter
{
public override Encoding Encoding
{
get { return new UnicodeEncoding(); }
}
public override void Write(char[] buffer, int index, int count)
{
Debug.WriteLine(new string(buffer, index, count));
}
}
Use it as such:
DataContext dc = new DataContext();
dc.Log = new DebugWriter();
How about having a many to many relationship (with a coupling table in between)? I cannot get my head wrapped around it. I know I have to repeat the trick but can you just join both tables or is it the complicated variant with a lot of braces? I get lost in the from and braces
In reply of my previous post:
var query = (from records in questions
join QuestionCategories in DataContextExtender.DataContext.QuestionCategories on records.ID equals QuestionCategories.questionID into tempQuestionCategories
from qc in tempQuestionCategories.DefaultIfEmpty()
select new { records.ID, records.title, records.description, records.sortOrder, records.showMonitoringActions, records.isDeleted, catID = (int?)(qc == null ? (int?)null : qc.categoryID) });
var sorted = (from Questions in query
join categories in DataContextExtender.DataContext.Categories on Questions.catID equals categories.ID into tempCats
from qcat in tempCats.DefaultIfEmpty()
select new { Questions.ID, Questions.title, Questions.description, Questions.sortOrder, Questions.showMonitoringActions, Questions.isDeleted, catTitle = (qcat == null ? "~~~~~~" + Questions.ID.ToString(): qcat.title ?? "~~~~~~" + Questions.ID.ToString())});
questions = (from a in sorted
join q in DataContextExtender.DataContext.Questions on a.ID equals q.ID
orderby a.catTitle, q.ID
select q).ToList();
now I hope a distinct will not disturb the sorting
The code means to sort questions (group them by) Category while there are many or none connected and it wants the questions without category on the bottom.
Post a Comment