I was writing a query that I want to get what's in one of my lists and what's in the other, and I encountered this error.

My code is here, when I type join instead of left join, it works fine, but the value I want is not coming, please help :D

 var orgRolOlanDuyurular = _context.DuyuruOrgRol.Include(x=>x.Duyuru).ThenInclude(l => l.KL_DuyuruTur).Where(l => l.Duyuru.AktifMi);
 var tumDuyurular = _context.Duyuru.Include(l => l.KL_DuyuruTur).Where(l => l.AktifMi);
 var c = tumDuyurular.LeftJoin(orgRolOlanDuyurular,
                                tmDuyurular => tmDuyurular.Id,
                                orgOlanDuyurular => orgOlanDuyurular.DuyuruId,
                                (tmDuyurular, orgOlanDuyurular) => new DuyuruPaging{Id = tmDuyurular.Id, BaslangicTarihi = tmDuyurular.BaslangicTarihi, BitisTarihi = tmDuyurular.BitisTarihi, DuyuruTurAd = tmDuyurular.KL_DuyuruTur.Ad, Konu = tmDuyurular.Konu });

CodePudding user response：

A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

There is no such syntax exactly the same as T-SQL to use Left or Right joins. However, there is a similar approach that you can leverage.

Assuming that you have two Entities. SalesOrderDetail and Product:

public partial class SalesOrderDetail
{
    public int SalesOrderID { get; set; }
    public short OrderQty { get; set; }
    public int ProductID { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal LineTotal { get; set; }
    public override string ToString()
    {
        StringBuilder sb = new StringBuilder(1024);

        sb.AppendLine($"Order ID: {SalesOrderID}");
        sb.Append($"   Product ID: {ProductID}");
        sb.AppendLine($"   Qty: {OrderQty}");
        sb.Append($"   Unit Price: {UnitPrice:c}");
        sb.AppendLine($"   Total: {LineTotal:c}");

        return sb.ToString();
    }
}

And Product as per below:

public partial class Product
{
    public int ProductID { get; set; }
    public string Name { get; set; }
    public string Color { get; set; }
    public decimal StandardCost { get; set; }
    public decimal ListPrice { get; set; }
    public string Size { get; set; }

    // Calculated Properties
    public int? NameLength { get; set; }
    public decimal? TotalSales { get; set; }
    public override string ToString()
    {
        StringBuilder sb = new StringBuilder(1024);

        sb.Append(Name);
        sb.AppendLine($"  ID: {ProductID}");
        sb.Append($"   Color: {Color}");
        sb.AppendLine($"   Size: {(Size ?? "n/a")}");
        sb.Append($"   Cost: {StandardCost:c}");
        sb.Append($"   Price: {ListPrice:c}");
        if (NameLength.HasValue)
        {
            sb.AppendLine($"   Name Length: {NameLength}");
        }
        if (TotalSales.HasValue)
        {
            sb.AppendLine($"   Total Sales: {TotalSales:c}");
        }
        return sb.ToString();
    }
}

Now Perform a left join between Products and Sales using DefaultIfEmpty() and SelectMany() as per below:

var query = (from prod in Products
         join sale in Sales
         on prod.ProductID equals sale.ProductID
           into sales
         from sale in sales.DefaultIfEmpty()
         select new
         {
             prod.ProductID,
             prod.Name,
             prod.Color,
             prod.StandardCost,
             prod.ListPrice,
             prod.Size,
             sale?.SalesOrderID,
             sale?.OrderQty,
             sale?.UnitPrice,
             sale?.LineTotal
         }).OrderBy(ps => ps.Name);

If you wish to use method syntax, you can achieve the same result as per below code:

var query = Products.SelectMany(
          sale =>
          Sales.Where(s => sale.ProductID == s.ProductID).DefaultIfEmpty(),
          (prod, sale) => new
          {
              prod.ProductID,
              prod.Name,
              prod.Color,
              prod.StandardCost,
              prod.ListPrice,
              prod.Size,
              sale?.SalesOrderID,
              sale?.OrderQty,
              sale?.UnitPrice,
              sale?.LineTotal
          }).OrderBy(ps => ps.Name);

Now you can use a simple foreach loop as per below:

foreach (var item in query)
{
    count  ;
    sb.AppendLine($"Product Name: {item.Name} ({item.ProductID})");
    sb.AppendLine($"  Order ID: {item.SalesOrderID}");
    sb.AppendLine($"  Size: {item.Size}");
    sb.AppendLine($"  Order Qty: {item.OrderQty}");
    sb.AppendLine($"  Total: {item.LineTotal:c}");
}

For more information, you can visit https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins