Linq .Contains with large set causes TDS error

Question

I've oversimplified this a bit, because I'm looking for a general-purpose answer. Let's say I've got a table setup like this:

Parent
recno    int (unique, pk)
date     datetime
stuff    varchar(50)

Child
parentrecno (int, fk)   --- PK
sequence    (int)       --- PK
data     varchar(50)

And in my C# program I've gone through a lot of hassle to find the Parent records that I'm interested in and stuff them into a list. Parent is a really large table, and I'd rather not query it more than necessary. So I squirrel away the keys:

List<int> recs = (from d in Parent where [.....] select d.recno).ToList();

Later on in Linq I can say, find all of the child records for the associated parents:

var kids = from k in database.Childs
      where recs.Contains(k.parentrecno)
      select new { k };

This is all great until recs contains more than 2100 entries. Then I get a TDS RPC error (too many parameters).

The way I see it I can:

• Do the whole thing in straight up SQL (didn't really want to do go through the hassle with a DataReader, etc...). There was an external system involved in qualifying the records, so I don't know if that's entirely possible either. Plus, I'd be generating that list twice -- once when I need to use it in .Contains(), and again for other purposes.

• Break the list (recs) up, and then read Child in chunks.

If I break it up in chunks, then my pretty Linq a little farther down won't work at all:

var kids2 = (from kid in paydb.Childs
         where
             recs.Contains(kid.parentrecno)
         group pay by kid.parentrecno into kgroup
         select new { ParentRecNo = kgroup.Key, KidRecords = kgroup })
              .ToDictionary(kx => kx.ParentRecNo);

Because the List recs will contain things that needed to be grouped together, but necessarily split apart for the Linq query.

Answer 1

Instead of using SQL and a DataReader you could also write two stored procedures and use those via LINQ. Or even read the list of id's via LINQ and feed that as input to your stored procedures.

You probably won't be able to solve the too many parameters issue (do you have access to the database) and the chunk solution isn't very nice and doesn't solve the whole problem because of the second query.

EDIT: Since the recs collection is not entirely database-generated, you need some way to tell your database the contents of this collection. I think your best option would be to use a stored procedure (or two) that accepts the collection as a large comma-separated string. Inside the stored procedure you split the string into ids again.

Some links that explain how to write and use a split string function.

By the way, if you are using SQL Server 2008, there is a far better approach than string parsing: table-valued parameters. You can pass a table as parameter to your stored procedure.

Answer 2

This looks like a job for Linq .Join() . I've used objects below instead of a database as the data source but the concept is the same if your LinqToSql provider supports it.

List<int> recs = new List<int>(Enumerable.Range(1, 2500));
List<Child> children = new List<Child>(Enumerable.Range(2000, 1000)
    .Select(x => new Child
    {
     ParentRecNo = x
    }));

var kids = children.Join(recs, x => x.ParentRecNo, y => y, (x, y) => x);

Console.WriteLine(kids.First().ParentRecNo);
Console.WriteLine(kids.Last().ParentRecNo);

output:

2000
2500

You can use the same Join in your Dictionary creation code as follows:

var kids2 = children
    .Join(recs, x => x.ParentRecNo, y => y, (x, y) => x)
    .GroupBy(x => x.ParentRecNo)
    .Select(kgroup => new
     {
      ParentRecNo = kgroup.Key,
      KidRecords = kgroup
     })
    .ToDictionary(kx => kx.ParentRecNo);

Answer 3

I think this is what you're looking for:

List<Child> children = 
database.Parents.Where( 'your favourite lambda expression').Select(x=>x.Childs).ToList();

So... I don't know what condition you're using for getting the parents, but hopefully it can be done with a lambda expression, such as:

List<Child> children = database.Parents
     .Where(p=>p.recno > 10 && p.recno < 40)
     .Select(x=>x.Childs).ToList();