Update Multiple Records Based on Multi-column Key

Question

I need to update multiple records based on a list of records to change. When that list is identified by one column, doing so is simple:

var fooIds = new List<int> { 2, 3, 4}
var foosToChange = Foo.Where(f => fooIds.Contains(f.Id));
// Update foosToChange and save here

What happens when the incoming list is an object with two properties that are needed to identity the record? For example:

var fooIds = new []
  {
    new { prop1 = "12345", prop2 = 2017111701 },
    new { prop1 = "hij", prop2 = 2018060101 }
  };

What would this need to become?

var foosToChange = Foo.Where(f => fooIds.Contains(???));

Is this even possible?

I think I know what you mean, but I don't think that will work. If you do contains on that first list, then on the second, you could be accepting any combination of the properties, when you really can only accept specific prop1 + prop2 combinations. — Bob Horn, Jun 25 '18 at 15:42
You would have to do it in 3 steps. Use a where+any to filter the records (see answer below), materialize the records from EF, and then do an in memory join so you can update the materialized records, and finally persist the changes back. — Igor, Jun 25 '18 at 15:49
I'm getting results by doing something like this: `var foos = from foo in foos join fooDb in Foo on foo.processId equals fooDb.ProcessId select fooDb;` That's because you can't join local to SQL, but you can do the reverse, based on this answer: https://stackoverflow.com/a/26966037/279516 — Bob Horn, Jun 25 '18 at 16:26

score 1 · Accepted Answer · answered Jun 26 '18 at 15:01

The query below will get the job done. If high performance is a must for the query, you could look into using a stored procedure with a Table Valued Parameter.

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

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

user007 · Answer 2 · 2018-06-25T15:50:25.010

0

It should be something like this:

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

Example:

        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

edited Jun 25 '18 at 15:50

answered Jun 25 '18 at 15:45

user007

1,122
1
10
30

Isn't example your use case? res contains element that you want to update. – user007 Jun 25 '18 at 15:51
I'm trying it now. I'm working through this error: `Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.` – Bob Horn Jun 25 '18 at 15:58
Oh, wait a minute. You're doing a `Where()` on `Foo`. I'm assuming that's your mock data. If so, what you're doing is all in memory, and that works. But it doesn't work when generating the SQL. Well, at least not LINQ to SQL, which is what LinqPad uses by default. I would need to get this to work with EF to see if it would work there... – Bob Horn Jun 25 '18 at 16:13

Bob Horn · Answer 3 · 2018-06-26T13:01:55.747

Note: This works, but it's terribly inefficient. Leaving this answer because it "works," but hopefully the newer answer is better.

Here is the LinqPad working code:

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; }
}

Doesn't this effectively do a `select * from foo` and then it performs the joins in memory?. If foo is a large table, performance will be terrible. — sgmoore, Jun 25 '18 at 17:38

score 0 · Answer 4 · answered Jun 26 '18 at 13:01

I'm hoping this is the final answer. It works, and its only inefficiency is that it will select a few more rows than necessary originally, but I'm willing to live with that.

LinqPad code:

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; }
}

Update Multiple Records Based on Multi-column Key

4 Answers4