List Join with a Table in LINQ-SQL Recommendation Request

Question

I have a list of account ids to retrieve from a different context and need to be able to do this:

List<Account> MyMethod(List<int> Accounts)
{
    List<Account> accounts = dc.Accounts.Join(Accounts,
                                              x => x.AccountID,
                                              y => y,
                                              (x, y) => x).ToList<Account>();
    return accounts;
}

It's obviously not a working query but what is the best way to handle this kind of request?

:::::UPDATE::::

Basically I came up with this solution but it's not complete:

accounts = (from a in dc.Accounts
                        where Accounts.Contains(a.AccountID)
                        select a).ToList<Account>();

However, how do you write it as a lambda-expression? I can't get Accounts.Contains(x=>x.AccountID) to work for obvious reasons.

Obviously? I understand the problem but I wouldn't say it is "obvious." One might argue that it is perfectly good LINQ code upon inspection. ;) — Jeff Mercado, May 03 '11 at 06:03
What obvious reasons? This is exactly what this linq query will get translated to: `dc.Accounts.Where(x => Accounts.Contains(x.AccoundID)).Tolist();` — configurator, May 04 '11 at 22:09

Jon Skeet · Accepted Answer · 2011-05-05T05:31:01.617

3

You can try:

List<Account> MyMethod(List<int> accounts)
{
    return dc.Accounts.Where(x => accounts.Contains(x.AccountID))
                      .ToList();
}

EDIT: Note that for more information about how query expressions are translated into non-query form, see this post in my Edulinq blog series.

edited May 05 '11 at 05:31

answered May 03 '11 at 06:02

Jon Skeet

1,421,763
867
9,128
9,194

it's complaining about time limit so i can't mark it as answered straight away otherwise i would have but thank you again anyway. – alexm May 03 '11 at 06:07
This works with a caveat ... if the list is too long this will blow up ... I think its blows up at around 100. for the record sql server supports about 2000 params – Sam Saffron May 04 '11 at 10:09
@Sam: Any idea why the LINQ to SQL limit is so much lower than SQL server itself? – Jon Skeet May 04 '11 at 10:09
@Jon I "think" it this is the reason http://www.sommarskog.se/dynamic_sql.html you are stuck passing in nvarchar(4000) in Sql Server 7 and 2000 ... so they went for the wider support. Seems a bit of an odd decision to me. (see the sp_executesql section) – Sam Saffron May 04 '11 at 10:14
Note actually max param capacity link is here: http://msdn.microsoft.com/en-us/library/ms143432.aspx – Sam Saffron May 04 '11 at 10:16

score 0 · Answer 2 · edited May 23 '17 at 11:47

0

You can use this solution: LINQ Expression to return Property value?

edited May 23 '17 at 11:47

Community

1
1

answered May 03 '11 at 06:54

Giedrius

8,430
6
50
91

List Join with a Table in LINQ-SQL Recommendation Request

2 Answers2