MVC +EF+linq 多表联查
关于linq的多表联查效果的实现：
后台多表查询 内连接：
SELECT
[Extent2].[partID] AS [partID],
[Extent1].[userName] AS [userName],
[Extent3].[partName] AS [partName]
FROM [dbo].[User] AS [Extent1]
INNER JOIN [dbo].[User_partB] AS [Extent2] ON [Extent1].[userGUID] = [Extent2].[userGUID]
INNER JOIN [dbo].[partB] AS [Extent3] ON [Extent2].[partID] = [Extent3].[partID]
数据库数据效果展示：
代码如下：
1 var userListTest = (from u in db.Users 2 join p in db.User_partB on u.userGUID 3 equals p.userGUID 4 join d in db.partBs on p.partID equals d.partID 5 select new { name = u.userName, departName = d.partName }); 6 List<dynamic> oneList = new List<dynamic>(); 7 foreach (var one in userListTest.ToList()) 8 { 9 dynamic dyObject = new ExpandoObject(); 10 dyObject.userName = one.name; 11 dyObject.departName = one.departName; 12 oneList.Add(dyObject); 13 } 14 ViewBag.dyObject = oneList; 15 return View();
前台数据展现效果：
1 <table> 2 <tr><td>用户登录名</td><td>姓名</td><td>状态</td><td>角色</td><td>操作</td></tr> 3 @{foreach (var one in ViewBag.dyObject) 4 { 5 <tr> 6 @*<td>@one.userLoginName</td>*@ 7 <td>@one.userName</td> 8 @*<td>@one.active==1?"启用":"禁用"</td>*@ 9 <td>@one.departName</td> 10 <td></td> 11 </tr> 12 } 13 } 14 </table>
linq 多表联查 外链接：
SELECT
1 AS [C1],
[Extent1].[userName] AS [userName],
[Extent3].[partName] AS [partName]
FROM [dbo].[User] AS [Extent1]
LEFT OUTER JOIN [dbo].[User_partB] AS [Extent2] ON [Extent1].[userGUID] = [Extent2].[userGUID]
LEFT OUTER JOIN [dbo].[partB] AS [Extent3] ON [Extent2].[partID] = [Extent3].[partID]
数据库数据展示效果：
代码如下：
1 var userListTest = (from u in db.Users 2 join p in db.User_partB on u.userGUID 3 equals p.userGUID into temp 4 from t in temp.DefaultIfEmpty() 5 join d in db.partBs on t.partID equals d.partID 6 into tempone 7 from user in tempone.DefaultIfEmpty() 8 select new { name = u.userName, departName = user.partName }); 9 List<dynamic> oneList = new List<dynamic>(); 10 foreach (var one in userListTest.ToList()) 11 { 12 dynamic dyObject = new ExpandoObject(); 13 dyObject.userName = one.name; 14 dyObject.departName = one.departName; 15 oneList.Add(dyObject); 16 } 17 ViewBag.dyObject = oneList; 18 return View();
前台数据展示效果同上
上述过程中，遇到 延迟加载后的动态数据，TOList()之后，传递到前台后，在foreach循环时，无法通过对象.属性的形式，读取出来，报“OBJECT未定义该属性”，经过查找原因发现，该动态数据为internal类型，只能同一程序集内访问，找了若干方法，不太适用，当然，如果大家有好的建议，可以一起交流讨论，究于当前场景，我选择了ExpandoObject，来动态处理，linq的查询结果。
