Geeks With Blogs
Etienne Giust's .NET notes Some .NET tips and tricks for me to remember. That might help you too

Here is a problem I encountered recently trying to perform a simple SQL operation using method syntax of Entity Framework.

I had 2 IEnumerable<Object> datasets (dataset1 and dataset2). I wanted to join them to obtain a third dataset without loosing dataset1 records not referencing dataset2 records. To put it simply, I needed a Left Join from dataset1 to dataset2.

The syntax would be straightforward in SQL; it would go like that :

SELECT D1.Id, D1.Whatever, D2.Id, D2.Something
FROM dataset1 D1
LEFT OUTER JOIN dataset2 D2

 

However, it is not so easy with Entity Framework. Here is a simple example of how to do it :

using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApplication1
{
    //  An example of SQL's LEFT OUTER JOIN equivalent using Entity Framework :
    //  Inputs : A dataset of Employees and a dataset of Offices. Each Employee record is holding an optional reference to its assigned Office.
    //  Goal : List the employees along with their (optional) assigned Office
    //
    //  Ex :
    //  Id FirstName Name       Office
    //  ---------------------------------
    //  1  John      Smith      New York
    //  2  Jean      Durand     Paris
    //  3  Oleg      Kouletchov
    //  4  Bobby     Lost
    //
    //

    // Item classes : Office, Employee and ResultClass
    public class Office
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
    public class Employee
    {
        public int Id { get; set; }
        public int? IdOffice { get; set; } // <- an office can be null
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
    public class ResultClass  // result records as we want them
    {
        public int IdEmployee { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string WorkPlace { get; set; }
    }
    

    class Program
    {
        static void Main(string[] args)
        {
            // populate datasets
            var listQueryableOffice = new List<Office>()
                                 {
                                     new Office() {Id = 1, Name = "New York"},
                                     new Office() {Id = 2, Name = "Paris"}
                                 }.AsQueryable();
            var listQueryableEmployees = new List<Employee>()
                                    {
                                        new Employee()
                                            {
                                                Id = 1,
                                                FirstName = "John",
                                                LastName = "Smith",
                                                IdOffice = 1
                                            },
                                            new Employee()
                                            {
                                                Id = 2,
                                                FirstName = "Jean",
                                                LastName = "Durand",
                                                IdOffice = 2
                                            },
                                             new Employee() // This guy has no workplace assigned yet
                                            {
                                                Id = 3,
                                                FirstName = "Oleg",
                                                LastName = "Kouletchov"
                                            },
                                             new Employee() // This guy has a reference to an Office that does not exist
                                            {
                                                Id = 4,
                                                FirstName = "Bobby",
                                                LastName = "Lost",
                                                IdOffice = 7
                                            },
                                    }.AsQueryable();
            //  Perform a GroupJoin followed by a SelectMany to obtain the following SQL Query equivalent with Entity Framework :
            //  SELECT E.Id, E.FirstName, E.LASTNAME, O.NAME as WORKPLACE
            //  FROM EMPLOYEES E
            //  LEFT OUTER JOIN OFFICE O
            IEnumerable<ResultClass> joinedResults =
                listQueryableEmployees.GroupJoin(listQueryableOffice,
                                                employee => employee.IdOffice, // perform the join on IdOffice
                                                office => office.Id,
                                                (employee, offices) =>
                                                new  // Intermediate anonymous type
                                                {
                                                    IdEmployee = employee.Id,
                                                    FirstName = employee.FirstName,
                                                    LastName = employee.LastName,
                                                    WorkPlaces = offices.DefaultIfEmpty() // IEnumerable<Office> , can be null
                                                })
                                    .SelectMany(
                                        x =>
                                        x.WorkPlaces.Select(   // each (possibly empty) WorkPlace record will yield a result record
                                            place => new ResultClass()  // the result type we want
                                            {
                                                IdEmployee = x.IdEmployee,
                                                FirstName = x.FirstName,
                                                LastName = x.LastName,
                                                WorkPlace = place == null ? "" : place.Name  // workplace may be null (LEFT JOIN here!) but we still return a record
                                            }));


            // Display the results
            foreach (var joinedResult in joinedResults)
            {
                Console.WriteLine(joinedResult.IdEmployee +
                    " " + joinedResult.FirstName +
                    " " + joinedResult.LastName + " " +
                    joinedResult.WorkPlace);
            }
            Console.ReadLine();
        }
    }
}
Posted on Friday, February 15, 2013 6:01 AM Entity Framework | Back to top


Comments on this post: Left join with Entity Framework (Method Syntax)

# re: Left join with Entity Framework (Method Syntax)
Requesting Gravatar...
totally pointless
Left by vba on Jan 25, 2016 2:29 AM

# re: Left join with Entity Framework (Method Syntax)
Requesting Gravatar...
Wow, thank you for this article! I needed to do a LEFT JOIN in EF and it kept giving me some crazy COUNT(*) sub-SELECT query, which has horrible performance characteristics (especially in the database I'm working with). The call to SelectMany was key to getting rid of that madness and having EF emit a simple LEFT OUTER JOIN. Thank you so much for saving me quite the headache!!
Left by Craig S on Feb 07, 2018 8:09 AM

Your comment:
 (will show your gravatar)


Copyright © Etienne Giust | Powered by: GeeksWithBlogs.net