I have this query, model can have multiple B and C. Model have 2 Cs which results in returning 2 models. Its supposed to only return 1 model with 1 A and a list of B and C Is is possible to achieve it in linq?

Model table has ModelId, Name

B table has BId, ModelId, Name

C table has CId, ModelId, Name

var x = 
    (from model in this.GetDefaultQuery()
    join a in _context.a on model.modelId equals a.modelId
    join b in _context.b on model.modelId equals b.modelId //this should be a list of b
    join c in _context.c on model.modelId equals c.modelId //this should be a distinc list of c
    select new
    {
        M = model,
        A = A,
        B = B, //this should be a list of B's Name
        C = C, //this should be a distinc list of C's Name
    })
    .Where(x => x.model.modelId == modelId);

CodePudding user response：

Why do you need JOIN must be the first question. With proper relations you don't even need it (and shouldn't).

What you are describing looks like to be:

var result = 
    (from model in this.GetDefaultQuery()
    select new
    {
        A = (from a in _context.a where model.aId == a.aId select a).ToList(),
        B = (from b in _context.b where model.bId == b.bId select b).ToList(),
        C = (from c in _context.c where model.cId == c.cId select c).Distinct().ToList()
    })
    .Where(x => x.modelId == modelId);

EDIT: It was hard to write in comprehension syntax. In method syntax it is easier:

var result = this.GetDefaultQuery()
      .Where(x => x.modelId == modelId)
      .Select(model => new {
          A = _context.a.Where(x => x.aId == model.aId).ToList(),
          B = _context.b.Where(x => x.bId == model.bId).Distinct().ToList(),
          C = _context.c.Where(x => x.cId == model.cId).ToList()
});

PS: Generated SQL would likely be very inefficient.

EDIT: Working sample. Note that connection string is what I have, and also your model doesn't really have navigation properties as you suggested, here I have navigation properties but not used to mimic your case. Otherwise those ctx.Customers.Where(...) would simply be o.Customer as an example.

string defaultConString = @"server=.\SQLExpress2012;Database=Northwind;Trusted_Connection=yes;";

void Main()
{
    var ctx = new MyContext(defaultConString);

    var result = ctx.Orders
        .Where(o => o.OrderId == 10249)
        .Select(o => new
        {
            A = ctx.Customers.Where(c => c.CustomerId == o.CustomerId).ToList(), // Single in fact. Could be SingleOrDefault()
            B = ctx.Employees.Where(e => e.EmployeeId == o.EmployeeId).ToList(),
            C = ctx.OrderDetails.Where(od => od.OrderId == o.OrderId).ToList()

        }

        );

    // Check result - Linqpad sample
    //result.Dump(); 
}


public class MyContext : DbContext
{
    public MyContext(string connectionString)
       : base(connectionString)
    { }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderDetail> OrderDetails { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Employee> Employees { get; set; }
}



public class Customer
{
    [Key]
    public string CustomerId { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    // ...
    //public virtual List<Order> Orders { get; set; }
}

public class Order
{
    [Key]
    public int OrderId { get; set; }
    public string CustomerId { get; set; }
    public int EmployeeId { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime? ShippedDate { get; set; }
    [ForeignKey("CustomerId")]
    public Customer Customer { get; set; }
    [ForeignKey("EmployeeId")]
    public Employee Employee { get; set; }
    public virtual List<OrderDetail> OrderDetails { get; set; }
}

[Table("Order Details")]
public class OrderDetail
{
    [Key]
    [Column(Order = 1)]
    public int OrderId { get; set; }
    [Key]
    [Column(Order = 2)]
    public int ProductId { get; set; }
    public decimal UnitPrice { get; set; }
    public Int16 Quantity { get; set; }
    [ForeignKey("ProductId")]
    public Product Product { get; set; }
    [ForeignKey("OrderId")]
    public Order Order { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    // ...
}

public class Employee
{
    [Key]
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}