none
linq left join syntax problem RRS feed

  • Question

  • Hi I want to use the left join to get all the records from the MainTransaction even the MaintransactionId does not exist in the table of SubTransaction.

    Also with other condition as like below, but this query only return the Maintransaction records if the MaintransactionId exist in the subtransaction table.

    How to achieve this ? Thanks

    from mt in t.maintransaction
    join st in t.subtransaction
      on mt.MainTransactionId equals st.MainTransactionId
      into sts
    where mt.IsEnabled
    where sts.Sum(x => x.Amount) - mt.Amount != 0
    select new
    {
      MainTransaction = mt,
      Subtransactions = sts,
    };
    
    


    Tuesday, August 23, 2011 9:08 AM

Answers

  • Hi again,

    As you are defining ms2 as your new variable, you can't use ms in your where condition. You should use ms2 instead.

    Anyway, as you are performing a left join operation, it is possible to get data rows without ms2 content, so you should check for null on your ms2 data usage.

    To get it working, try first to run your query without your where condition, and then add it again if all works well.

    Regards,

    JA Reyes.

     


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Tuesday, August 23, 2011 2:27 PM

All replies

  • Hi,

    You must use DefaultIfEmpty as in this sample code:

     

    var lstFacturas = new List<Factura> 
         { 
         new Factura {IdFactura = 1, Importe = 500 }, 
         new Factura {IdFactura = 2, Importe = 1200 }, 
         new Factura {IdFactura = 3, Importe = 400 }, 
         new Factura {IdFactura = 4, Importe = 3500 } 
         }; 
    
    var lstDescuentos = new List<FacturaDescuento> 
         { 
          new FacturaDescuento {IdFactura = 2, Descuento = 20 }, 
          new FacturaDescuento {IdFactura = 4, Descuento = 100 } 
         }; 
     
    
    var lstLeftJoin = 
     (from fact in lstFacturas 
     join desc in lstDescuentos on fact.IdFactura equals desc.IdFactura into FactDesc 
     from fd in FactDesc.DefaultIfEmpty() 
     select new
       { 
       IdFactura = fact.IdFactura, 
       ImporteBase = fact.Importe, 
       ImporteACobrar = (fd == null) ? fact.Importe : fact.Importe - fd.Descuento 
       } 
     ).ToList(); 
    

     

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Tuesday, August 23, 2011 10:01 AM
  • Hi Thanks for your help,

    When I tried your method I got the error.

     

        protected object GetMainTbl()
        {
    
          var o = from mt in t.maintransaction
              join st in t.subtransaction
                on mt.MainTransactionId equals st.MainTransactionId
              into ms
              from ms2 in ms.DefaultIfEmpty()
              where mt.IsEnabled == true && ms.Sum(x => x.Amount) - mt.Amount != 0
              select ms2;
    
          return o;
    }


     

    LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1[TransactionModel.subtransaction] DefaultIfEmpty[subtransaction](System.Collections.Generic.IEnumerable`1[TransactionModel.subtransaction])' method, and this method cannot be translated into a store expression.

    What maybe wrong ?

    Tuesday, August 23, 2011 1:08 PM
  • Hi again,

    As you are defining ms2 as your new variable, you can't use ms in your where condition. You should use ms2 instead.

    Anyway, as you are performing a left join operation, it is possible to get data rows without ms2 content, so you should check for null on your ms2 data usage.

    To get it working, try first to run your query without your where condition, and then add it again if all works well.

    Regards,

    JA Reyes.

     


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Tuesday, August 23, 2011 2:27 PM