如何在linq中的select（）中包含groupby（）元素到SQL查询... [英] How do how to include the groupby() elements in select() in linq to SQL queryi...

问题描述

我有一个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();

< br $>


现在，如果我想在Select（）中添加GroupBy成员x.EmployeeCode，它需要它，并且工作正常，但我不知道如何将导航属性x.EmployeeMaster.Emp_FullName包含到Select（）中，是GroupBy（）的下一个成员。我知道我在Select（）中引用的模型PY_History_TransactionTAB不包含Emp_FullName的定义，但它包含导航属性的定义，即EmployeeMaster。



所以，有没有办法将一个导航属性x.EmployeeMaster.Emp_FullName包含在Select（）中，以便我可以在强类型视图中访问它们？



所以，我的问题是如何修改我的linq到sql查询以获得正确的结果视图？



我尝试了什么：



我不知道如何在c＃中执行此操作，我尝试将导航属性放在select（）中并且它说PY_History_TransactionTAB不包含Emp_FullName的定义。

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.

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?

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

What I have tried:

I'm unaware of how to do this in c# , I've tried by putting the navigation property in the select() and it says that PY_History_TransactionTAB does not contain definition of Emp_FullName.

推荐答案

正如评论中所讨论的那样，因为你正在重用 PY_History_TransactionTAB 类，而且它没有t包含 Emp_FullName 属性，你需要提取来自组中第一条记录的 EmployeeMaster ：

As discussed in the comments, since you're reusing the PY_History_TransactionTAB class, and it doesn't contain an Emp_FullName property, you'll need to extract the EmployeeMaster from the first record in the group:

FilteredReport = ReportData.Select(x => new PY_History_TransactionTAB
                {
                    EmployeeCode = x.Key.EmployeeCode,
                    EmployeeMaster = x.First().EmployeeMaster, // <-- Add this line.
                    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();

如果我理解正确的话......



假设 Emp_FullName 字段不属于 PY_History_TransactionTAB 表，您必须加入 EmployeeMaster 表才能访问它。



请参阅：

如何：使用连接（Visual Basic）将数据与LINQ结合使用 [ ^ ]

join子句（C＃参考） [ ^ ]

基本LINQ查询操作（C＃） [ ^ ]

如何：执行分组连接（C＃编程指南） [ ^ ]

如何：执行内部联接（C＃编程指南） [ ^ ]

如何：执行左外连接（C＃编程指南） [ ^ ]

If i understand you correctly...

Assuming that Emp_FullName field doesn't belong to PY_History_TransactionTAB table, you have to join EmployeeMaster table to be able to access it.

See:
How to: Combine Data with LINQ by Using Joins (Visual Basic)[^]
join clause (C# Reference)[^]
Basic LINQ Query Operations (C#)[^]
How to: Perform Grouped Joins (C# Programming Guide)[^]
How to: Perform Inner Joins (C# Programming Guide)[^]
How to: Perform Left Outer Joins (C# Programming Guide)[^]

这篇关于如何在linq中的select（）中包含groupby（）元素到SQL查询...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！