原

EF Group Join简单运用

8830人阅读 2020/6/3 11:02 总访问：688037 评论：0 收藏：0 手机

分类: EF

EF Group Join其实返回的就是一对多的情况，通常用来做有查看详情的，比如我们来实现一个查询学生与考试分数的例子，因为一个学生可以考试很多科目所以可以使用selectMany来实现。

代码很简单：

/// <summary>
/// 查询用户和分数
/// </summary>
/// <returns></returns>
public ActionResult UserScore()
{
    oapEntities oap = new oapEntities();
    List<UserScore> userScore = oap.Users.GroupJoin(oap.Score, a => a.Id, b => b.UsersId, (user, scores) => new UserScore
    {
        user = user,
        scores = scores
    }).ToList();
    return View(userScore);
}

前台，就展示数据即可

@{
    ViewBag.Title = "我的分数";
    Layout = "~/views/_layoutpage.cshtml";
}
<script src="~/Content/js/myplugins.js"></script>
<script src="http://static.runoob.com/assets/jquery-validation-1.14.0/dist/jquery.validate.min.js"></script>
<script src="~/Content/js/jquery.validate.unobtrusive.min.js"></script>
<script src="~/Content/pagebar/js/jqPaginator.js"></script>
<link href="~/Content/pagebar/css/page.css" rel="stylesheet" />
<script src="~/Content/layer/layer.js"></script>
<script>

    $(function () {
        $(".showdetails").click(function () {

            var tds = $(this).parents("tr").find("td");

            layer.open({
                title: "分数详情",
                content: tds.eq(5).html()
            });

        });
    });
</script>

@using EFLearn2.Models
@model List<UserScore>

<div class="row-fluid">

    <form method="post" id="searchform" action="/home/index">
        <input type="text" placeholder="请输入用户名" name="username" value="@ViewBag.username" />
        <input type="text" placeholder="请输入学号" name="number" value="@ViewBag.number" />
        <input type="hidden" name="page" id="pagehidden" />
        <select id="checktypes" name="checktype">
            <option value="-1">请选择审核状态</option>
            <option value="1">通过</option>
            <option value="2">审核中</option>
            <option value="3">审核失败</option>
        </select>
        <button class="btn" style="margin-top: -10px; padding: 5px 10px" id="search">查询</button>
    </form>
</div>

<div>
    <a href="#" class="btn mini batdeletea" style="background-color: #ff6666; color: #fff"><i class="icon-trash"></i>删除</a>
    <a href="#" class="btn mini batupdate" style="background-color: #aaaaff; color: #fff"><i class="icon-star"></i>保存</a>
    <a href="#" class="btn mini batchcancel" style="background-color: #aacc66; color: #fff"><i class="icon-share"></i>撤销</a>
    <a href="#" class="btn mini adda" style="background-color: #ff99ff; color: #fff"><i class="icon-plus"></i>添加</a>
    <a href="#" class="btn mini batchedit" style="background-color: #aaaaff; color: #fff"><i class="icon-star"></i>编辑</a>
</div>

<div class="portlet-body" style="margin-top: 3px">

    <table id="userTable" class="table table-striped table-bordered table-advance table-hover ">

        <thead>
            <tr>
                <th>
                    <input type="checkbox" id="headcheck" />
                </th>
                <th><i class="icon-briefcase"></i>编号</th>
                <th><i class="icon-briefcase"></i>姓名</th>
                <th class="hidden-phone"><i class="icon-user"></i>学号</th>
                <th><i class="icon-shopping-cart"></i>班级</th>
                <th><i class="icon-shopping-cart"></i>操作</th>
            </tr>
        </thead>
        <tbody>

            @foreach (UserScore item in Model)
            {
                <tr>
                    <td>
                        <input type="checkbox" value="@item.user.Id" />
                    </td>
                    <td>@item.user.Id</td>
                    <td>@item.user.UserName</td>
                    <td>@item.user.Number</td>
                    <td>@item.user.UClass</td>
                    <td style="display: none">
                        @foreach (var score in item.scores)
                        {
                            <div>@score.Sub:@score.Score1</div>
                        }
                    </td>
                    <td style="width: 109px">
                        <a href="#" class="showdetails">分数</a>
                        <a href="#" class="">宠物</a>
                        <a href="#" class="deletea">删除</a>
                    </td>
                </tr>
            }

        </tbody>

    </table>
    <ul class="pagination" id="pagination" style="margin-top: 10px; float: right"></ul>
</div>

先用一个隐藏域放着在用layer弹出来就行了，效果如下：

当然我们可以来更多表的group join比如来个4表的

public ActionResult UserScore()
{
    oapEntities oap = new oapEntities();
    //四表的group join
    List<UserScore> userScore = oap.Users.GroupJoin(oap.Score, a => a.Id, b => b.UsersId, (user, scores) => new
    {
        user = user,
        scores = scores
    }).GroupJoin(oap.Pet, a => a.user.Id, b => b.UserId, (before, pets) => new
    {
        user = before.user,
        scores = before.scores,
        pets = pets
    }).GroupJoin(oap.User_Parent, a => a.user.Id, b => b.UsersId, (a, parents) => new UserScore
    {
        user = a.user,
        pets = a.pets,
        scores = a.scores,
        parents = parents
    }).ToList();
    return View(userScore);
}

前台一样的只是可以一次展示更多数据而已

@{
    ViewBag.Title = "我的分数";
    Layout = "~/views/_layoutpage.cshtml";
}
<script src="~/Content/js/myplugins.js"></script>
<script src="http://static.runoob.com/assets/jquery-validation-1.14.0/dist/jquery.validate.min.js"></script>
<script src="~/Content/js/jquery.validate.unobtrusive.min.js"></script>
<script src="~/Content/pagebar/js/jqPaginator.js"></script>
<link href="~/Content/pagebar/css/page.css" rel="stylesheet" />
<script src="~/Content/layer/layer.js"></script>
<script>

    $(function () {
        $(".showdetails").click(function () {

            var tds = $(this).parents("tr").find("td");

            layer.open({
                title: "分数详情",
                content: tds.eq(5).html()
            });

        });

        $(".showpet").click(function () {

            var tds = $(this).parents("tr").find("td");

            layer.open({
                title: "宠物详情",
                content: tds.eq(6).html()
            });

        });

        $(".showparents").click(function () {

            var tds = $(this).parents("tr").find("td");

            layer.open({
                title: "家长详情",
                content: tds.eq(7).html()
            });

        });



    });
</script>

@using EFLearn2.Models
@model List<UserScore>

<div class="row-fluid">

    <form method="post" id="searchform" action="/home/index">
        <input type="text" placeholder="请输入用户名" name="username" value="@ViewBag.username" />
        <input type="text" placeholder="请输入学号" name="number" value="@ViewBag.number" />
        <input type="hidden" name="page" id="pagehidden" />
        <select id="checktypes" name="checktype">
            <option value="-1">请选择审核状态</option>
            <option value="1">通过</option>
            <option value="2">审核中</option>
            <option value="3">审核失败</option>
        </select>
        <button class="btn" style="margin-top: -10px; padding: 5px 10px" id="search">查询</button>
    </form>
</div>

<div>
    <a href="#" class="btn mini batdeletea" style="background-color: #ff6666; color: #fff"><i class="icon-trash"></i>删除</a>
    <a href="#" class="btn mini batupdate" style="background-color: #aaaaff; color: #fff"><i class="icon-star"></i>保存</a>
    <a href="#" class="btn mini batchcancel" style="background-color: #aacc66; color: #fff"><i class="icon-share"></i>撤销</a>
    <a href="#" class="btn mini adda" style="background-color: #ff99ff; color: #fff"><i class="icon-plus"></i>添加</a>
    <a href="#" class="btn mini batchedit" style="background-color: #aaaaff; color: #fff"><i class="icon-star"></i>编辑</a>
</div>

<div class="portlet-body" style="margin-top: 3px">

    <table id="userTable" class="table table-striped table-bordered table-advance table-hover ">

        <thead>
            <tr>
                <th>
                    <input type="checkbox" id="headcheck" />
                </th>
                <th><i class="icon-briefcase"></i>编号</th>
                <th><i class="icon-briefcase"></i>姓名</th>
                <th class="hidden-phone"><i class="icon-user"></i>学号</th>
                <th><i class="icon-shopping-cart"></i>班级</th>
                <th><i class="icon-shopping-cart"></i>操作</th>
            </tr>
        </thead>
        <tbody>

            @foreach (UserScore item in Model)
            {
                <tr>
                    <td>
                        <input type="checkbox" value="@item.user.Id" />
                    </td>
                    <td>@item.user.Id</td>
                    <td>@item.user.UserName</td>
                    <td>@item.user.Number</td>
                    <td>@item.user.UClass</td>
                    <td style="display: none">
                        @foreach (var score in item.scores)
                        {
                            <div>@score.Sub:@score.Score1</div>
                        }
                    </td>
                    <td style="display: none">
                        @foreach (var pet in item.pets)
                        {
                            <div>@pet.CatName:@pet.DogName</div>
                        }
                    </td>
                    <td style="display: none">
                        @foreach (var parent in item.parents)
                        {
                            <div>@parent.Father:@parent.Mother</div>
                        }
                    </td>
                    <td style="width: 139px">
                        <a href="#" class="showdetails">分数</a>
                        <a href="#" class="showpet">宠物</a>
                        <a href="#" class="showparents">家长</a>
                        <a href="#" class="deletea">删除</a>
                    </td>
                </tr>
            }

        </tbody>

    </table>
    <ul class="pagination" id="pagination" style="margin-top: 10px; float: right"></ul>
</div>