如何在Linq to Sql查询的Select()中包括GroupBy()元素 [英] How to include the GroupBy() elements in Select() in Linq to Sql query

问题描述

我有一个Linq to sql查询:

I've a Linq to sql query:

IEnumerable<PY_History_TransactionTAB> FilteredReport;

var ReportData = db.PY_History_TransactionTAB.AsEnumerable()
                 .Where(x => x.SystemCode == SysCode)
                 .GroupBy(x => new 
                 { 
                   x.EmployeeCode,
                   x.EmployeeMaster.Emp_FullName,
                   x.Designations.Title,
                   department = x.Departments.Title
                 });

FilteredReport = ReportData.Select(x => new PY_History_TransactionTAB
                {
                    EmployeeCode = x.Key.EmployeeCode,
                    H_SalaryDays = x.Sum(y => y.H_SalaryDays ?? 0),
                    H_NET_Overtime = x.Sum(y => y.H_NET_Overtime),
                    H_Overtime_Amount = x.Sum(y => y.H_Overtime_Amount),
                    H_SL_Breakup1 = x.Sum(y => y.H_SL_Breakup1 ?? 0),                       
                    H_OT_Allowance1 = x.Sum(y => y.H_OT_Allowance1 ?? 0),                      
                }).ToList();

现在，如果我想在Select()中添加GroupBy成员x.EmployeeCode，它将采用它并可以正常工作，但是我不知道如何在Select()中包括导航属性x.EmployeeMaster.Emp_FullName.是GroupBy()的下一个成员.我知道我在Select()中引用的模型PY_History_TransactionTAB不包含Emp_FullName的定义，但是包含导航属性(即EmployeeMaster)的定义.

Now, if I want to add the GroupBy member x.EmployeeCode in Select(), it takes it, and works fine, but I don't know how to include a navigation property x.EmployeeMaster.Emp_FullName to Select(), which is a next member of GroupBy(). I know that the model PY_History_TransactionTAB to which I am refering in Select() does not contain definition for Emp_FullName but it contains definition for a navigation property i.e. EmployeeMaster.

那么，有没有办法将导航属性x.EmployeeMaster.Emp_FullName包含到Select()中，以便我可以在强类型视图中访问它们?

So, Is there a way to include a navigation property x.EmployeeMaster.Emp_FullName to the Select() so that I can access them in a strongly typed view?

为方便起见，以下是我的观点:

Below is my View just for convenience :

@model IEnumerable<HrAndPayrollSystem.Models.PY_History_TransactionTAB>
        <tr>                
            <th>
                @Html.DisplayNameFor(model => model.EmployeeCode)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Departments.Title)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.EmployeeMaster.Emp_FullName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Designations.Title)
            </th>             
            <th>
                @Html.DisplayNameFor(model => model.H_SalaryDays)
            </th>
            .
            .
            . // Other Elements
       </tr>

在上面的视图中，我可以访问model.EmployeeCode，但是我无法访问的是model.EmployeeMaster.Emp_FullName和类似的其他导航属性.

In the above View, I can access model.EmployeeCode but what I cannot access is model.EmployeeMaster.Emp_FullName and similarly other navigation properties.

所以，我的问题是如何将linq修改为sql查询以正确显示结果视图?

So, my question is that how do I modify my linq to sql query to get the result view right?

推荐答案

以这种方式修改您的查询:

Modify your query this way :

FilteredReport = ReportData.Select(x => new PY_History_TransactionTAB
            {
                EmployeeCode = x.Key.EmployeeCode,
                EmployeeMaster = x.First().EmployeeMaster,
                .
                .    // other attributes
                .
            }).ToList();

这篇关于如何在Linq to Sql查询的Select()中包括GroupBy()元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！