基于多列键更新多条记录

下面的查询将完成任务。如果查询必须使用高性能,则可以使用 Table Valued Parameter 是的。

var keys = fooIds.Select(f => f.prop1 + "." + f.prop2);

Foo.Where(f => keys.Contains(f.prop1 + "." + f.prop2))

应该是这样的:

var foosToChange = Foo.Where(f => fooIds.Any(x => x.prop1 == f.Id1 && x.prop2 == f.Id2));

例子:

        A[] Foo = new A[]{ new A{ Id1 = "12345", Id2 = "2017111701" }, new A { Id1 = "fakeid", Id2 = "2017111701" } };

        var res = Foo.Where(f => fooIds.Any(x => x.prop1 == f.Id1 && x.prop2 == f.Id2));

        // res will return first element

我希望这是最后的答案。它可以工作,而且它唯一的低效是它将比原来需要的多选择几行,但我愿意接受这一点。

LINQPAD代码:

void Main()
{
    var payrolls = new List<PayrollKey>
      {
        new PayrollKey { CompanyId = "12345", ProcessId = 2017111701 },
        new PayrollKey { CompanyId = "hij", ProcessId = 2018060101 }
      };    

    // Store just the companyIds from the incoming list
    var companyIds = payrolls.Select(x => x.CompanyId);

    // From the DB, get all the foos for the companies in the list.
    // We will be getting rows for all processIds for a company, but that's ok because:
    // A) I'm guessing that's not super common, and B) they'll be filtered-out in the next query.
    var allFoosForCompanies =
      from foo in Foo
      where foo.Status == "Open" && companyIds.Contains(foo.CompanyId)
      select foo;

    // Now, from the two in-memory lists, get only the foos we care about
    // (having the correct processId).
    var foosToChange =
      from payroll in payrolls
      join foo in allFoosForCompanies on
        new { CompanyId = payroll.CompanyId, ProcessId = payroll.ProcessId }
        equals 
        new { CompanyId = foo.CompanyId, ProcessId = foo.ProcessId }
      where foo.Status == "Open"
      select foo;

    // Now make the change and save.
    foreach(var foo in foosToChange)
    {
      foo.Status = "Sent";
    }
    SubmitChanges();
}
 
public class PayrollKey
{
  public string CompanyId { get; set; }
  public int ProcessId { get; set; }
}

注意:这很有效,但效率非常低。留下这个答案是因为它“有效”,但希望新的答案更好。

这是linqpad的工作代码:

void Main()
{
    var fooToChange = new List<FooClass>
      {
        new FooClass { CompanyId = "12345", ProcessId = 2017111701 },
        new FooClass { CompanyId = "hij", ProcessId = 2018060101 }
      };    

    var foos =
      from foo in fooToChange
      join fooDb in Foo on
        new { CompanyId = foo.CompanyId, ProcessId = foo.ProcessId }
        equals 
        new { CompanyId = fooDb.CompanyId, ProcessId = fooDb.ProcessId }
      select fooDb;

    foreach(var foo in foos)
    {
      foo.Status = "Sent";
    }

    SubmitChanges();
}

public class FooClass
{
  public string CompanyId { get; set; }
  public int ProcessId { get; set; }
}