c# - 从同一个表中过滤数据的问题

问题描述

我有一个像下面这样的课程：

 public class MasterTransaction 
    {
        public int Id { get; set; }
        public int EmployeeId { get; set; }
        public int? SubTransactionId { get; set; }

        [ForeignKey("SubTransactionId")]
        public virtual MasterTransaction SubTransaction { get; set; }
        public virtual ICollection<CommissionForManager> CommissionForManager { get; set; }
    }
    
    public class CommissionForManager
    {
        public int Id { get; set; }
        public int ManagerId { get; set; }
        public decimal CommissionMoney { get; set; }
        
         public int MasterTransactionId { get; set; }

        [ForeignKey("MasterTransactionId")]
        public virtual MasterTransaction MasterTransaction { get; set; }
    }

MasterTransaction将存储所有主交易。 CommissionForManager表存储与经理从中获得佣金的所有交易相关的数据。

样本数据：

MasterTransaction:

Id   EmployeeId     SubTransactionId
50   100            null  //100 is normal employee            
51   101            50    //101 is a Manager
52   102            null


CommissionForManager:

Id  ManagerId   CommissionMoney MasterTransactionId
1   101         5000            50  
2   101         6000            52

现在 Manager 从 Employee 的事务创建事务，在这种情况下，“ SubTransactionId”列将保存该TransactionId事务的。

例如：经理 101 从交易 50 创建交易，因此SubTransactionId持有价值 50。

因此，我想获取经理获得佣金的交易列表，但不应在“ SubTransactionId”列中引用这些特定交易。

例如：事务 ID = 50 和 51，因为 51 是从 50 创建的，所以我想忽略两者。我只想要事务 52，因为它没有SubTransactionId在另一个事务的“”列中被引用。

询问：

string searchString;
int managerId;
var query = context.CommissionForManager.Where(c => c.CommissionMoney > 0)
                                        .Where(c=> c.MasterTransaction.Employee.EmployeeName.Contains(searchString));
                                        
if (managerId > 0)
    query = query.Where(c=>c.ManagerId == managerId);
    
return query.ToList();

但是在这里，我不知道如何在同一个 MasterTransaction 表的 SubTransactionId 中再次搜索 Transaction Id？

有人可以帮助我或指导我完成:)

标签： c#entity-frameworklinqentity-framework-6

这将不会从 CommissionForManager 中选择 MasterTransactionId 出现在 MasterTransaction 的 SubTransactionId 列中的行

var query = context.CommissionForManager.Where(c => c.CommissionMoney > 0)
    .Where(c=> c.MasterTransaction.Employee.EmployeeName.Contains(searchString))
    .Where(c => !context.MasterTransaction.Where(row => row.SubTransactionId == c.MasterTransactionId).Any());

此代码将执行与上面相同的操作，但生成左外连接而不是嵌套选择：

            var query = from c in context.CommissionForManagers
                     join m in context.MasterTransactions on c.MasterTransactionId equals m.SubTransactionId
                     into joined from j in joined.DefaultIfEmpty()      // Without this a inner join will be performed instead of a left join
                     where c.CommissionMoney > 0
                     && j == null                   // Select only rows that havent been joined to a MasterTransactions row by SubTransactionId
                     //&& c.MasterTransactions.Employee.EmployeeName.Contains(searchString)
                     select c;

生成的 SQL：

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[ManagerId] AS [ManagerId], 
[Extent1].[CommissionMoney] AS [CommissionMoney], 
[Extent1].[MasterTransactionId] AS [MasterTransactionId]
FROM  [dbo].[CommissionForManager] AS [Extent1]
LEFT OUTER JOIN [dbo].[MasterTransaction] AS [Extent2] ON ([Extent1].[MasterTransactionId] = [Extent2].[SubTransactionId]) OR (([Extent1].[MasterTransactionId] IS NULL) AND ([Extent2].[SubTransactionId] IS NULL))
WHERE ([Extent1].[CommissionMoney] > 0) AND ([Extent2].[Id] IS NULL)

c# - 从同一个表中过滤数据的问题

问题描述

解决方案

推荐阅读