ASP.NET MVC中LINQ实现SQL等价多表关联查询的问题求助

示例参考SQL（基于典型数据仓库关联逻辑） #

SELECT 
    loc.LocationID, loc.LocationName,
    inv.InventoryQuantity,
    prod.ProductID, prod.ProductName,
    dt.DateKey, dt.FullDate,
    tm.TimeKey, tm.Hour
FROM FactInventory inv
JOIN DimLocation loc ON inv.LocationKey = loc.LocationKey
JOIN DimProductInventory prod ON inv.ProductInventoryKey = prod.ProductInventoryKey
JOIN DimDate dt ON inv.DateKey = dt.DateKey
JOIN DimTime tm ON inv.TimeKey = tm.TimeKey
WHERE inv.InventoryQuantity > 0
ORDER BY loc.LocationName, dt.FullDate

步骤1：定义强类型ViewModel #

解决匿名类型导致的视图兼容性问题，创建专用接收模型：

public class InventoryViewModel
{
    public int LocationID { get; set; }
    public string LocationName { get; set; }
    public int InventoryQuantity { get; set; }
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int DateKey { get; set; }
    public DateTime FullDate { get; set; }
    public int TimeKey { get; set; }
    public int Hour { get; set; }
}

步骤2：Controller中的LINQ查询（查询语法） #

实现与SQL等价的多表关联，投影到ViewModel避免类型转换错误：

public ActionResult InventoryReport()
{
    var inventoryData = from inv in db.FactInventory
                        join loc in db.DimLocation on inv.LocationKey equals loc.LocationKey
                        join prod in db.DimProductInventory on inv.ProductInventoryKey equals prod.ProductInventoryKey
                        join dt in db.DimDate on inv.DateKey equals dt.DateKey
                        join tm in db.DimTime on inv.TimeKey equals tm.TimeKey
                        where inv.InventoryQuantity > 0
                        orderby loc.LocationName, dt.FullDate
                        select new InventoryViewModel
                        {
                            LocationID = loc.LocationID,
                            LocationName = loc.LocationName,
                            InventoryQuantity = inv.InventoryQuantity,
                            ProductID = prod.ProductID,
                            ProductName = prod.ProductName,
                            DateKey = dt.DateKey,
                            FullDate = dt.FullDate,
                            TimeKey = tm.TimeKey,
                            Hour = tm.Hour
                        };

    return View(inventoryData.ToList());
}

步骤3：Controller中的LINQ查询（方法语法） #

偏好链式写法的等价实现：

public ActionResult InventoryReport()
{
    var inventoryData = db.FactInventory
        .Join(db.DimLocation,
              inv => inv.LocationKey,
              loc => loc.LocationKey,
              (inv, loc) => new { inv, loc })
        .Join(db.DimProductInventory,
              combined => combined.inv.ProductInventoryKey,
              prod => prod.ProductInventoryKey,
              (combined, prod) => new { combined.inv, combined.loc, prod })
        .Join(db.DimDate,
              combined => combined.inv.DateKey,
              dt => dt.DateKey,
              (combined, dt) => new { combined.inv, combined.loc, combined.prod, dt })
        .Join(db.DimTime,
              combined => combined.inv.TimeKey,
              tm => tm.TimeKey,
              (combined, tm) => new { combined.inv, combined.loc, combined.prod, combined.dt, tm })
        .Where(x => x.inv.InventoryQuantity > 0)
        .OrderBy(x => x.loc.LocationName)
        .ThenBy(x => x.dt.FullDate)
        .Select(x => new InventoryViewModel
        {
            LocationID = x.loc.LocationID,
            LocationName = x.loc.LocationName,
            InventoryQuantity = x.inv.InventoryQuantity,
            ProductID = x.prod.ProductID,
            ProductName = x.prod.ProductName,
            DateKey = x.dt.DateKey,
            FullDate = x.dt.FullDate,
            TimeKey = x.tm.TimeKey,
            Hour = x.tm.Hour
        })
        .ToList();

    return View(inventoryData);
}

步骤4：视图类型声明 #

在视图顶部指定强类型模型，解决IEnumerable兼容性问题：

@model IEnumerable<YourProjectNamespace.InventoryViewModel>

@foreach (var item in Model)
{
    <div>
        <span>@item.LocationName</span>
        <span>@item.ProductName</span>
        <span>库存数量：@item.InventoryQuantity</span>
        <span>日期：@item.FullDate.ToString("yyyy-MM-dd")</span>
    </div>
}

常见问题修复 #

• 类型转换错误：禁止返回匿名类型，使用强类型ViewModel投影是核心解决方法，匿名类型无法被视图强类型识别，必然导致转换失败。
• IEnumerable兼容性错误：确保Controller返回List<InventoryViewModel>（已实现IEnumerable接口），且视图模型声明与返回类型完全匹配即可解决。

内容的提问来源于stack exchange，提问作者Chris Singleton