首页 文章

C＃LINQ在两个不同的数据集上使用条件where子句连接

提问于
浏览 次
2

我有两个我正在比较数据的集合 . 我可以通过ID加入这两个集合 . 我需要一个where子句，它返回一个数据列表，其中集合B中的某些项目未在集合A中找到

public class Contract
    {
        public string ContractId { get; set; }
        public IList InvoiceList { get; set; }

        public class Invoice
        {
            public int InvoiceNumber { get; set; }
        } 
    }

    public class PaymentRequest
    {
        public IList Contracts { get; set; }
        public class ContractList
        {       
            public string ContractId { get; set; }  
            public IList Invoices { get; set; }
        }

        public class InvoiceList
        {          
            public int InvoiceNumber { get; set; }
        }
    }

到目前为止我有以下内容，但不能完全得到where子句 .

var query = (
    from request in (
        from contract in paymentRequest.Contracts                    
        from invoice in contract.Invoices
        select new { contract, invoice })
    join valid in (
        from contract in validContracts
        select new { contract })               
    on new { request.contract.ContractId } equals new { valid.contract.ContractId }
    where !(
        // get a list of invoice numbers in the request data set that are not in the valid data set
    )
    select "Contract Id: " + request.contract.ContractId +
           ", Invoice Number: " + request.invoice.InvoiceNumber
).ToList();

任何帮助表示赞赏 .

c# .net linq

3 回答

  • 1
    var collectionAIds = new HashSet<int>(collectionA.Select(colAItem => colAItem.Id));
var itemsInCollectionBNotInCollectionA = collectionB.Where(colBItem => 
                                             !collectionAIds.Contains(colBItem.Id));

    基本上，我们想要在集合A中获取ID，然后从集合B中选择所有项目，而不是在A的ID列表中 .

    HashSet是可选的 . 如果你不使用那个变量，它只是避免重复的O（n）查找 .

    附：我假设int，作为id的类型..使用Hashset的id的数据类型 .

    回复于
  • 0

    使用 contains 可以降低性能，而不是提醒常规Sql查询 . 这会更好

    var result = 
    from itm1 in Coll1
    from itm2 in Coll2.Where(x => x.Id == itm1.Id).DefaultIfEmpty()
    where itm2 == null
    select itm1;

    这将为您提供_222426_中Coll2中不存在的所有项目 . 这比任何时候使用_722427都要快

    回复于
  • 0

    在我看来，您的查询应如下所示：

    var query =
(
    from request in
    (
        from contract in paymentRequest.Contracts                    
        from invoice in contract.Invoices
        select new { contract, invoice }
    )
    join valid in
    (
        from contract in validContracts
        select new { contract }
    ) on new { request.contract.ContractId } equals new { valid.contract.ContractId } into gvs
    where
        gvs
            .SelectMany(gv => gv.contract.Invoices)
            .Select(i => i.InvoiceNumber)
            .All(n => n != request.invoice.InvoiceNumber)
    select "Contract Id: " + request.contract.ContractId +
           ", Invoice Number: " + request.invoice.InvoiceNumber
).ToList();
    回复于

相关问题