LINQ嵌套查询

Question

我有一个具有以下

var myvar = from table in MyDataModel 
      where..... 
      select new MyModel 
      { 
        modelvar1 = ..., 
        modelvar2 = (from..... into anothervar) 
      } 

我想要做的是有modelvar2是结果我从目前得到anothervar在MyDataModel另一个表之间连接的查询。

谢谢

Answer 1

括号看起来更像是一个子查询而不是连接。这是你如何做一个加入。
来自AdventureWorks database的示例表格。

using (DataClasses1DataContext context = new DataClasses1DataContext()) 
{ 
    // If you have foreign keys correctly in your database you can 
    // join implicitly with the "dot" notation. 
    var myvar = from prod in context.Products 
       where prod.ListPrice < 10 
       select new 
       { 
        Name = prod.Name, 
        Category = prod.ProductSubcategory.ProductCategory.Name, 
       }; 

    // If you don't have foreign keys you need to express the join 
    // explicitly like this 
    var myvar2 = from prod in context.Products 
       join prodSubCategory in context.ProductSubcategories 
       on prod.ProductSubcategoryID equals prodSubCategory.ProductSubcategoryID 
       join prodCategory in context.ProductCategories 
       on prodSubCategory.ProductCategoryID equals prodCategory.ProductCategoryID 
       where prod.ListPrice < 10 
       select new 
       { 
        Name = prod.Name, 
        Category = prodCategory.Name, 
       }; 

    // If you REALLY want to do a subquery, this is how to do that 
    var myvar3 = from prod in context.Products 
       where prod.ListPrice < 10 
       select new 
       { 
        Name = prod.Name, 
        Category = (from prodSubCategory in context.ProductSubcategories 
           join prodCategory in context.ProductCategories 
           on prodSubCategory.ProductCategoryID equals prodCategory.ProductCategoryID 
           select prodCategory.Name).First(), 
       }; 

    // If you want to get a list from the subquery you can do like this 
    var myvar4 = from prodCategory in context.ProductCategories 
       select new 
       { 
        Name = prodCategory.Name, 
        Subcategoreis = (from prodSubCategory in context.ProductSubcategories 
            where prodSubCategory.ProductCategoryID == prodCategory.ProductCategoryID 
            select new { prodSubCategory.ProductSubcategoryID, prodSubCategory.Name }).ToList(), 
       }; 

} 

Answer 2

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry] 
FROM [Orders] AS [t0] 
INNER JOIN ([Order Details] AS [t1] 
    INNER JOIN [Products] AS [t2] ON [t1].[ProductID] = [t2].[ProductID]) ON [t0].[OrderID] = [t1].[OrderID] 

可以写为

from o in Orders 
join od in (
    from od in OrderDetails join p in Products on od.ProductID equals p.ProductID select od) 
    on o.OrderID equals od.OrderID 
select o