- c - 在位数组中找到第一个零
- linux - Unix 显示有关匹配两种模式之一的文件的信息
- 正则表达式替换多个文件
- linux - 隐藏来自 xtrace 的命令
长期潜伏者,第一次海报。我过去在这里找到了一些很好的答案,所以我想我会来这里看看我是否能得到一点帮助!
我是 Linq 的新手,我正在为我的对象使用 Entity Framework 。我的项目中有一个 .edmx 文件。
首先,我从 VS 2010 附带的示例页面导入了 using System.Linq.Dynamic 类,因此我可以将其添加到我的页面中:
使用 System.Linq.Dynamic;
问题是,我认为我的连接运行不正常。
这是我当前的代码:
private void FetchData()
{
using (var Context = new ProjectEntities())
{
var Query =
Context.Users
.Join(Context.UserStats, // Table to Join
u => u.msExchMailboxGuid, // Column to Join From
us => us.MailboxGuid, // Column to Join To
(u, us) => new // Alias names from Tables
{
u,
us
})
.Join(Context.TechContacts, // Table to Join
u => u.u.UserPrincipalName, // Column to Join From
tc => tc.UPN, // Column to Join To
(u, tc) => new // Alias names from Tables
{
u = u,
tc = tc
})
.Where(u => true)
.OrderBy("u.u.CompanyName")
.Select("New(u.u.CompanyName,tc.UPN,u.us.TotalItemSize)");
// Add Extra Filters
if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text)))
{
Query = Query.Where("u.CompanyName.Contains(@0)", SearchCompanyNameTextBox.Text);
}
// Set the Record Count
GlobalVars.TotalRecords = Query.Count();
// Add Paging
Query = Query
.Skip(GlobalVars.Skip)
.Take(GlobalVars.Take);
// GridView Datasource Binding
GridViewMailboxes.DataSource = Query;
GridViewMailboxes.DataBind();
}
}
我如何编写它才能像在普通 SQL 中那样工作?
SELECT u.Column1,
u.Column2,
us.Column1,
tc.Column1
FROM Users AS u
INNER JOIN UserStats AS us
ON u.msExchMailboxGuid = us.MailboxGuid
INNER JOIN TechContacts AS tc
ON u.UserPrincipalName = tc.UPN
我需要保留动态的 .Where 子句和 .Select 字段名称,您现在看到的问题是我需要执行 u.u.CompanyName 以取回 u.CompanyName 字段,因为它在我的连接中出现了两次。
我已经在谷歌上搜索了一段时间,但还没有找到答案。
非常感谢任何帮助!
编辑 - 这是我当前的查询。它有效,但看起来有点像噩梦。
请耐心等待。如果可以的话,我想在这里包含所有内容,即使它有点多。
动态列选择对我来说是必须的。否则我还不如坚持使用我的表适配器和存储过程。能够减少我的查询以返回更少的数据是我的目标之一。如果有人可以提出改进建议,我会洗耳恭听吗?
我无法找到一种方法来停止必须将我的连接选择到子项中,在 SQL 中,当我连接时,我只需要通过我的 SELECT 语句返回我想要的列。
private void FetchData()
{
using (var Context = new ProjectEntities())
{
string Fields = GetDynamicFields();
var Query =
Context.Users
.Join(Context.UserStats, // Table to Join
u => u.msExchMailboxGuid, // Column to Join From
us => us.MailboxGuid, // Column to Join To
(u, us) => new // Declare Columns for the next Join
{
ObjectGuid = u.objectGuid,
msExchMailboxGuid = u.msExchMailboxGuid,
CompanyName = u.CompanyName,
ResellerOU = u.ResellerOU,
DisplayName = u.DisplayName,
MBXServer = u.MBXServer,
MBXSG = u.MBXSG,
MBXDB = u.MBXDB,
MBXWarningLimit = u.MBXWarningLimit,
MBXSendLimit = u.MBXSendLimit,
MBXSendReceiveLimit = u.MBXSendReceiveLimit,
extensionAttribute10 = u.extensionAttribute10,
legacyExchangeDN = u.legacyExchangeDN,
UserPrincipalName = u.UserPrincipalName,
Mail = u.Mail,
lastLogonTimeStamp = u.lastLogonTimestamp,
createTimeStamp = u.createTimeStamp,
modifyTimeStamp = u.modifyTimeStamp,
altRecipient = u.altRecipient,
altRecipientBL = u.altRecipientBL,
DeletedDate = u.DeletedDate,
MailboxGuid = us.MailboxGuid,
Date = us.Date,
AssociatedItemCount = us.AssociatedItemCount,
DeletedItemCount = us.DeletedItemCount,
ItemCount = us.ItemCount,
LastLoggedOnUserAccount = us.LastLoggedOnUserAccount,
LastLogonTime = us.LastLogonTime,
StorageLimitStatus = us.StorageLimitStatus,
TotalDeletedItemSize = us.TotalDeletedItemSize,
TotalItemSize = us.TotalItemSize,
MailboxDatabase = us.MailboxDatabase
})
.Join(Context.TechContacts, // Table to Join
u => u.UserPrincipalName, // Column to Join From
tc => tc.UPN, // Column to Join To
(u, tc) => new // Declare Final Column Names
{
ObjectGuid = u.ObjectGuid,
msExchMailboxGuid = u.msExchMailboxGuid,
CompanyName = u.CompanyName,
ResellerOU = u.ResellerOU,
DisplayName = u.DisplayName,
MBXServer = u.MBXServer,
MBXSG = u.MBXSG,
MBXDB = u.MBXDB,
MBXWarningLimit = u.MBXWarningLimit,
MBXSendLimit = u.MBXSendLimit,
MBXSendReceiveLimit = u.MBXSendReceiveLimit,
extensionAttribute10 = u.extensionAttribute10,
legacyExchangeDN = u.legacyExchangeDN,
UserPrincipalName = u.UserPrincipalName,
Mail = u.Mail,
lastLogonTimeStamp = u.lastLogonTimeStamp,
createTimeStamp = u.createTimeStamp,
modifyTimeStamp = u.modifyTimeStamp,
altRecipient = u.altRecipient,
altRecipientBL = u.altRecipientBL,
DeletedDate = u.DeletedDate,
MailboxGuid = u.MailboxGuid,
Date = u.Date,
AssociatedItemCount = u.AssociatedItemCount,
DeletedItemCount = u.DeletedItemCount,
ItemCount = u.ItemCount,
LastLoggedOnUserAccount = u.LastLoggedOnUserAccount,
LastLogonTime = u.LastLogonTime,
StorageLimitStatus = u.StorageLimitStatus,
TotalDeletedItemSize = u.TotalDeletedItemSize,
TotalItemSize = u.TotalItemSize,
MailboxDatabase = u.MailboxDatabase,
// New Columns from this join
UPN = tc.UPN,
Customer_TechContact = tc.Customer_TechContact,
Customer_TechContactEmail = tc.Customer_TechContactEmail,
Reseller_TechContact = tc.Reseller_TechContact,
Reseller_TechContactEmail = tc.Reseller_TechContact,
Reseller_Name = tc.Reseller_Name
})
.Where(u => true)
.OrderBy(GlobalVars.SortColumn + " " + GlobalVars.SortDirection)
.Select("New(" + Fields + ")");
// Add Extra Filters
if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text)))
{
Query = Query.Where("CompanyName.StartsWith(@0)", SearchCompanyNameTextBox.Text);
}
// Set the Record Count
GlobalVars.TotalRecords = Query.Count();
// Add Paging
Query = Query
.Skip(GlobalVars.Skip)
.Take(GlobalVars.Take);
// GridView Datasource Binding
GridViewMailboxes.DataSource = Query;
GridViewMailboxes.DataBind();
}
}
这是在后台运行的SQL:
SELECT TOP (20)
[Project1].[C1] AS [C1],
[Project1].[objectGuid] AS [objectGuid],
[Project1].[msExchMailboxGuid] AS [msExchMailboxGuid],
[Project1].[CompanyName] AS [CompanyName],
[Project1].[ResellerOU] AS [ResellerOU],
[Project1].[DisplayName] AS [DisplayName],
[Project1].[MBXServer] AS [MBXServer],
[Project1].[MBXSG] AS [MBXSG],
[Project1].[MBXDB] AS [MBXDB],
[Project1].[MBXWarningLimit] AS [MBXWarningLimit],
[Project1].[MBXSendLimit] AS [MBXSendLimit],
[Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit],
[Project1].[extensionAttribute10] AS [extensionAttribute10],
[Project1].[legacyExchangeDN] AS [legacyExchangeDN],
[Project1].[UserPrincipalName] AS [UserPrincipalName],
[Project1].[Mail] AS [Mail],
[Project1].[lastLogonTimestamp] AS [lastLogonTimestamp],
[Project1].[createTimeStamp] AS [createTimeStamp],
[Project1].[modifyTimeStamp] AS [modifyTimeStamp],
[Project1].[altRecipient] AS [altRecipient],
[Project1].[altRecipientBL] AS [altRecipientBL],
[Project1].[DeletedDate] AS [DeletedDate]
FROM ( SELECT [Project1].[objectGuid] AS [objectGuid],
[Project1].[msExchMailboxGuid] AS [msExchMailboxGuid],
[Project1].[CompanyName] AS [CompanyName],
[Project1].[ResellerOU] AS [ResellerOU],
[Project1].[DisplayName] AS [DisplayName],
[Project1].[MBXServer] AS [MBXServer],
[Project1].[MBXSG] AS [MBXSG],
[Project1].[MBXDB] AS [MBXDB],
[Project1].[MBXWarningLimit] AS [MBXWarningLimit],
[Project1].[MBXSendLimit] AS [MBXSendLimit],
[Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit],
[Project1].[extensionAttribute10] AS [extensionAttribute10],
[Project1].[legacyExchangeDN] AS [legacyExchangeDN],
[Project1].[UserPrincipalName] AS [UserPrincipalName],
[Project1].[Mail] AS [Mail],
[Project1].[lastLogonTimestamp] AS [lastLogonTimestamp],
[Project1].[createTimeStamp] AS [createTimeStamp],
[Project1].[modifyTimeStamp] AS [modifyTimeStamp],
[Project1].[altRecipient] AS [altRecipient],
[Project1].[altRecipientBL] AS [altRecipientBL],
[Project1].[DeletedDate] AS [DeletedDate],
[Project1].[C1] AS [C1],
row_number() OVER (ORDER BY [Project1].[CompanyName] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[objectGuid] AS [objectGuid],
[Extent1].[msExchMailboxGuid] AS [msExchMailboxGuid],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ResellerOU] AS [ResellerOU],
[Extent1].[DisplayName] AS [DisplayName],
[Extent1].[MBXServer] AS [MBXServer],
[Extent1].[MBXSG] AS [MBXSG],
[Extent1].[MBXDB] AS [MBXDB],
[Extent1].[MBXWarningLimit] AS [MBXWarningLimit],
[Extent1].[MBXSendLimit] AS [MBXSendLimit],
[Extent1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit],
[Extent1].[extensionAttribute10] AS [extensionAttribute10],
[Extent1].[legacyExchangeDN] AS [legacyExchangeDN],
[Extent1].[UserPrincipalName] AS [UserPrincipalName],
[Extent1].[Mail] AS [Mail],
[Extent1].[lastLogonTimestamp] AS [lastLogonTimestamp],
[Extent1].[createTimeStamp] AS [createTimeStamp],
[Extent1].[modifyTimeStamp] AS [modifyTimeStamp],
[Extent1].[altRecipient] AS [altRecipient],
[Extent1].[altRecipientBL] AS [altRecipientBL],
[Extent1].[DeletedDate] AS [DeletedDate],
1 AS [C1]
FROM [dbo].[Users] AS [Extent1]
INNER JOIN [dbo].[UserStats] AS [Extent2] ON [Extent1].[msExchMailboxGuid] = [Extent2].[MailboxGuid]
INNER JOIN [dbo].[TechContacts] AS [Extent3] ON [Extent1].[UserPrincipalName] = [Extent3].[UPN]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 120
ORDER BY [Project1].[CompanyName] ASC
最佳答案
不是太多的答案,而是建议。首先,去抢LinqPad .在进行查询调优时,这是无价的。其次,我敢打赌您会因为使用 .Join 而收到大量查询。 Linq2Entities 有一个讨厌的习惯,就是每次进行连接时都会创建投影(子查询)。我会花一些时间在 LinqPad 和我的查询上,直到我得到我想要的查询。
关于c# - Entity Framework 查询中的多个连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9621995/
我知道这个问题可能已经被问过,但我检查了所有这些,我认为我的情况有所不同(请友善)。所以我有两个数据集,第一个是测试数据集,第二个是我保存在数据框中的预测(预测值,这就是没有数据列的原因)。我想合并两
在 .loc 方法的帮助下,我根据同一数据框中另一列中的值来识别 Panda 数据框中某一列中的值。 下面给出了代码片段供您引用: var1 = output_df['Player'].loc[out
当我在 Windows 中使用 WinSCP 通过 Ubuntu 连接到 VMware 时,它提示: The server rejected SFTP connection, but it lis
我正在开发一个使用 xml web 服务的 android 应用程序。在 wi-fi 网络中连接时工作正常,但在 3G 网络中连接时失败(未找到 http 404)。 这不仅仅发生在设备中。为了进行测
我有一个XIB包含我的控件的文件,加载到 Interface Builder(Snow Leopard 上的 Xcode 4.0.2)中。 文件的所有者被设置为 someClassController
我在本地计算机上管理 MySQL 数据库,并通过运行以下程序通过 C 连接到它: #include #include #include int main(int argc, char** arg
我不知道为什么每次有人访问我网站上的页面时,都会打开一个与数据库的新连接。最终我到达了大约 300 并收到错误并且页面不再加载。我认为它应该工作的方式是,我将 maxIdle 设置为 30,这意味着
希望清理 NMEA GPS 中的 .txt 文件。我当前的代码如下。 deletes = ['$GPGGA', '$GPGSA', '$GPGSV', '$PSRF156', ] searchquer
我有一个 URL、一个用户名和一个密码。我想在 C# .Net WinForms 中建立 VPN 连接。 你能告诉我从哪里开始吗?任何第三方 API? 代码示例将受到高度赞赏... 最佳答案 您可以像
有没有更好的方法将字符串 vector 转换为字符 vector ,字符串之间的终止符为零。 因此,如果我有一个包含以下字符串的 vector "test","my","string",那么我想接收一
我正在编写一个库,它不断检查 android 设备的连接,并在设备连接、断开连接或互联网连接变慢时给出回调。 https://github.com/muddassir235/connection_ch
我的操作系统:Centos 7 + CLOUDLINUX 7.7当我尝试从服务器登录Mysql时 [root@server3 ~]# Mysql -u root -h localhost -P 330
我收到错误:Puma 发现此错误:无法打开到本地主机的 TCP 连接:9200(连接被拒绝 - 连接(2)用于“本地主机”端口 9200)(Faraday::ConnectionFailed)在我的
请给我一些解决以下错误的方法。 这是一个聊天应用....代码和错误如下:: conversations_controller.rb def create if Conversation.bet
我想将两个单元格中的数据连接到一个单元格中。我还想只组合那些具有相同 ID 的单元格。 任务 ID 名称 4355.2 参与者 4355.2 领袖 4462.1 在线 4462.1 快速 4597.1
我经常需要连接 TSQL 中的字段... 使用“+”运算符时 TSQL 强制您处理的两个问题是 Data Type Precedence和 NULL 值。 使用数据类型优先级,问题是转换错误。 1)
有没有在 iPad 或 iPhone 应用程序中使用 Facebook 连接。 这个想法是登录这个应用程序,然后能够看到我的哪些 facebook 用户也在使用该应用程序及其功能。 最佳答案 是的。
我在连接或打印字符串时遇到了一个奇怪的问题。我有一个 char * ,可以将其设置为字符串文字的几个值之一。 char *myStrLiteral = NULL; ... if(blah) myS
对于以下数据 - let $x := "Yahooooo !!!! Select one number - " let $y := 1 2 3 4 5 6 7 我想得到
我正在看 UDEMY for perl 的培训视频,但是视频不清晰,看起来有错误。 培训展示了如何使用以下示例连接 2 个字符串: #!usr/bin/perl print $str = "Hi";
我是一名优秀的程序员,十分优秀!