var query = (from u in entityt.用户表 join b in entity.权限表 on u.用户权限Id equls b.Id where u.用户Id == 1 select u).FirstOrDefault();
private DataClasses1DataContext dc;
dc = new DataClasses1DataContext();
var heji = (from da in dc.q1
join du in dc.q2
on da.danhao equals du.danhao
where da.hetong == "XXXX"
select new { du.color1, da.danhao, da.riqi, du.zhongliang, du.beizhu }).Distinct();
关于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]
- var userListTest = (from u in db.Users
- join p in db.User_partB on u.userGUID
- equals p.userGUID
- join d in db.partBs on p.partID equals d.partID
- select new { name = u.userName, departName = d.partName });
- List<dynamic> oneList = new List<dynamic>();
- foreach (var one in userListTest.ToList())
- {
- dynamic dyObject = new ExpandoObject();
- dyObject.userName = one.name;
- dyObject.departName = one.departName;
- oneList.Add(dyObject);
- }
- ViewBag.dyObject = oneList;
- return View();
前台数据数据表示的方法:
- <table>
- <tr><td>用户登录名</td><td>姓名</td><td>状态</td><td>角色</td><td>操作</td></tr>
- @{foreach (var one in ViewBag.dyObject)
- {
- <tr>
- @*<td>@one.userLoginName</td>*@
- <td>@one.userName</td>
- @*<td>@one.active==1?"启用":"禁用"</td>*@
- <td>@one.departName</td>
- <td></td>
- </tr>
- }
- }
- </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]
- var userListTest = (from u in db.Users
- join p in db.User_partB on u.userGUID
- equals p.userGUID into temp
- from t in temp.DefaultIfEmpty()
- join d in db.partBs on t.partID equals d.partID
- into tempone
- from user in tempone.DefaultIfEmpty()
- select new { name = u.userName, departName = user.partName });
- List<dynamic> oneList = new List<dynamic>();
- foreach (var one in userListTest.ToList())
- {
- dynamic dyObject = new ExpandoObject();
- dyObject.userName = one.name;
- dyObject.departName = one.departName;
- oneList.Add(dyObject);
- }
- ViewBag.dyObject = oneList;
- return View();
前台数据展示效果同上
上述过程中,遇到 延迟加载后的动态数据,TOList()之后,传递到前台后,在foreach循环时,无法通过对象.属性的形式,读取出来,报“OBJECT未定义该属性”,经过查找原因发现,该动态数据为internal类型,只能同一程序集内访问,找了若干方法,不太适用,当然,如果大家有好的建议,可以一起交流讨论,究于当前场景,我选择了ExpandoObject,来动态处理,linq的查询结果。