Converting a LEFT OUTER JOIN to Entity Framework

by

The basic syntax for a “left join” in Linq is like this:

from x in table1
join y in table2 on x.id equals y.id into jointable
from z in jointable.DefaultIfEmpty()
select new
{
  x.Field1, 
  x.Field2,
  x.Field3,
  Field4 = z == null ? 0 : z.Field4
};

In your case, I’m a little confused because the entity relations you seem to be using in your Linq don’t match the ones implied by your SQL; are the relationships here zero-or-one, zero-or-many, one-to-one, etc? Specifically, you’re doing this:

from h in context.Authentications
join t in context.Authorizations on h.AuthenticationId equals t.Authentications.AuthenticationId

but your SQL implies that “Authentication” is the parent here with zero-or-more “Authorization” children, not the other way around, which would be more like:

from h in context.Authentications
from t in h.Authorizations.DefaultIfEmpty()

If you can give us a better idea of the data model and what data you expect to get out of it we can more easily explain how that query would look in Linq. Assuming that your relationships match what is implied by the SQL, you should be able to get what you want using the following Linq queries:

var query = from h in context.Authentications
            from t in h.Authorizations.DefaultIfEmpty()
            select new
            {
                h.AuthenticationId,
                h.Username,
                Permissions = t == null ? null : t.Permissions,
                Organizations = t == null ? new EntitySet<Organization>() : t.Organizations
            };

var query2 = from x in query
             from o in x.organizations.DefaultIfEmpty()
             select new
             {
                 AUTHENTICATION_ID = (short?)x.AuthenticationId,
                 x.Username,
                 x.Permissions,
                 OrganizationIdentifier = o == null ? null : o.OrganizationIdentifier,
                 OrganizationID = o == null ? (short?)null : o.OrganizationID 
             };

More Related Contents:

  1. sort in Ascending Order [closed]
  2. How to get DateTime from the internet?
  3. Using a custom type discriminator to tell JSON.net which type of a class hierarchy to deserialize
  4. Random number in long range, is this the way?
  5. How does one configure HttpClient not to automatically redirect when it receives a 301 HTTP Status Code?
  6. Convert an int to bool with Json.Net [duplicate]
  7. Does not contain a constructor that takes 0 arguments
  8. C# 4.0 optional out/ref arguments
  9. HttpClient vs HttpWebRequest
  10. Best ORM to use with C# 4.0 [closed]
  11. TPL TaskFactory.FromAsync vs Tasks with blocking methods
  12. Entity Framework and forced Inner Join
  13. How to create a method at runtime using Reflection.emit
  14. Drag-and-Drop multiple Attached File From Outlook to C# Window Form
  15. What are the true benefits of ExpandoObject?
  16. Conflicting overloaded methods with optional parameters
  17. Parse Math Expression [duplicate]
  18. How to use jquery or ajax to update razor partial view in c#/asp.net for a MVC project
  19. Redirect from asp.net web api post action
  20. How to encode the plus (+) symbol in a URL
  21. Contract-First SOA: Designing Business Domain: WCF
  22. C#: Dynamic runtime cast
  23. Entity Framework – Is there a way to automatically eager-load child entities without Include()?
  24. Adding unknown (at design time) properties to an ExpandoObject
  25. Why classes that implement variant interfaces remain invariant?
  26. DataGridView column footer c#.net winforms
  27. Duck type testing with C# 4 for dynamic objects
  28. mapping multiple tables to a single entity class in entity framework
  29. How to send data in jquery.post to mvc controller which use ViewModel as parameter?
  30. How to get content type of a web address?

Leave a Comment