none
How to do multiple left outer joins in an L2S Query? RRS feed

  • Question

  • A little background on the query below. Cell has a 1:M to Container and a 1:M with Printer. I want a query that will retrieve all Cells and associated containers, if they exist, and associated printers, if they exist. Essentially I want to do a left outer join on both tables. Here is the query I have:

    var query = from cell in Cell
        join container
    in Container.Where (row => row.SerialNumber == "1102141") on cell.CellID equals container.CellID
       
    into containers

        join printer
    in Printer.Where (row => row.Name == "PG10RelWarrPrt3") on cell.CellID equals printer.CellID
       
    into printers

       
    select new { Cell = cell, Containers = containers, Printers = printers };

    query
    .Dump();
    This query works, but is not efficient. It does a left outer join on Container, but, for each Cell, it performs a separate query to retrieve any Printer rows, instead of also doing a left outer join on Printer.

    How can I change this so that it also does a left outer join on the Printer table? BTW, I want a hierarchical result set. IOW, each Cell should have a list of containers and a list of printers. Each would be empty of course, if none existed for the cell.

    Wednesday, November 24, 2010 1:40 PM

Answers

  • If you want to do left joins, use .DefaultIfEmpty():

    from cell in Cell
    join container in (
     from c in Container
     where c.SerialNumber == "1102141"
      && c.CellID == cell.CellID
     select c
     ).DefaultIfEmpty()
    join printer in (
     from p in Printers
     where p.Name == "PG10RelWarrPrt3"
      && p.CellID == cell.CellID
     select p
     ).DefaultIfEmpty()
    where cell.xxxxx == someCriteria
    select new { Cell = cell, Containers = container, Printers = printer };
    

    This will however result in a flattened resultset with cells repeated for each container/printer.

    Another option is to use the LoadWith method; this will make L2S retrieve all in one big join but materialize into entityrefs/entitysets. This requires that you have FKs/associations between cell/printer/container, but will not give you the ability to add additional filters on the child-tables.

    E.g.:

    DataLoadOptions dl = new DataLoadOptions();
    dl.LoadWith<Cell>(c => c.Container);
    dl.LoadWith<Cell>(p => p.Printer);
    dc.LoadOptions = dl;
    
    var cells = from cell in Cells where ... select cell;
    

     

    A third option (which can often be more efficient) is to hit the DB with separate queries, in this case one to get all cells, then one to gell all printers, and then one to get all containers (but with the appropriate joins to get the ones relevant for the cells you're retrieving).


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by liurong luo Monday, December 6, 2010 10:40 AM
    Friday, November 26, 2010 10:01 AM
    Answerer

All replies

  • Hi,

    Look if this other thread can help you:

    http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/927b7f0b-3ed7-4707-8ce3-3c77dfb35eb8

    Regards,

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    Wednesday, November 24, 2010 3:42 PM
  • If you want to do left joins, use .DefaultIfEmpty():

    from cell in Cell
    join container in (
     from c in Container
     where c.SerialNumber == "1102141"
      && c.CellID == cell.CellID
     select c
     ).DefaultIfEmpty()
    join printer in (
     from p in Printers
     where p.Name == "PG10RelWarrPrt3"
      && p.CellID == cell.CellID
     select p
     ).DefaultIfEmpty()
    where cell.xxxxx == someCriteria
    select new { Cell = cell, Containers = container, Printers = printer };
    

    This will however result in a flattened resultset with cells repeated for each container/printer.

    Another option is to use the LoadWith method; this will make L2S retrieve all in one big join but materialize into entityrefs/entitysets. This requires that you have FKs/associations between cell/printer/container, but will not give you the ability to add additional filters on the child-tables.

    E.g.:

    DataLoadOptions dl = new DataLoadOptions();
    dl.LoadWith<Cell>(c => c.Container);
    dl.LoadWith<Cell>(p => p.Printer);
    dc.LoadOptions = dl;
    
    var cells = from cell in Cells where ... select cell;
    

     

    A third option (which can often be more efficient) is to hit the DB with separate queries, in this case one to get all cells, then one to gell all printers, and then one to get all containers (but with the appropriate joins to get the ones relevant for the cells you're retrieving).


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by liurong luo Monday, December 6, 2010 10:40 AM
    Friday, November 26, 2010 10:01 AM
    Answerer
  • Hello Randy,

    Have you tried the suggestion? How about the result? Please let me know if you need more help when you feel free! Thanks a lot!


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Monday, November 29, 2010 8:03 AM