Select "IN" in LINQ to SQL

Question

I get a list of entities to update and I have their ids. I want to get the original ones from the database, so I do:

String[] ids = updatedEvents.Select(ue => ue.id).ToArray();

var originalEventsToUpdate = Db.tbl_ffk_event
                               .Where(e => ids.Contains(e.id))
                               .ToArray();

But what I get using the log is this generated SQL:

SELECT [t0].[id], [t0].[fs_mapping_id], [t0].[fs_id_value], [t0].[desc]
FROM [dbo].[tbl_ffk_event] AS [t0]
WHERE 0 = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

And that SQL means "get the whole table".

How can I generate a "IN" like this:

SELECT [t0].[id], [t0].[fs_mapping_id], [t0].[fs_id_value], [t0].[desc]
FROM [dbo].[tbl_ffk_event] AS [t0]
WHERE [t0].[id] IN ('aaa','bbb','ccc','ddd','eee',)

Thanks in advance.

EDIT:

I feel stupid, I didn't see the WHERE 0 = 1. It's because at that point, there where nothing in the ids collection. I have checked out now ensuring there are items, and the SQL is generated correctly. Sorry.

You said, "And that SQL means 'get the whole table'." I think you meant, 'Get nothing'. That WHERE clause is always false. — Mike Sherrill 'Cat Recall', Mar 28 '11 at 11:09
Duplicate of http://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause — Mike Sherrill 'Cat Recall', Mar 28 '11 at 11:20
vtortola, I wouldn't feel stupid over that one, since the format of the SQL is (while correct for what it has to do) somewhat unexpected. It's sensible enough SQL I suppose though, considering that `WHERE [t0].[id] IN ()` would be invalid. Clever ol' linq ;) — Jon Hanna, Mar 28 '11 at 11:28

score 2 · Accepted Answer · answered Mar 28 '11 at 11:11

2

Actually, due to the clause WHERE 0 = 1 this SQL will return an empty recordset (i.e. correctly mapped in terms of the schema, but with no rows).

The code you give seems correct, but something has convinced the query provider that there can never be a matching row.

Assuming it's not correct in this, I'd look at the column mapping for the id property. Does it match that of the database correctly?

answered Mar 28 '11 at 11:11

Jon Hanna

110,372
10
146
251

I thought it says 'WHERE 1 = 1' I didn't realize that. – vtortola Mar 28 '11 at 11:15
1

What about the mapping, did it reveal the problem as I suspected (if not, maybe add it to your question). – Jon Hanna Mar 28 '11 at 11:16

score 0 · Answer 2 · answered Dec 28 '12 at 13:50

0

Probably your list is coming empty, this is the normal behavior of Linq.

answered Dec 28 '12 at 13:50

Carlos Lemos

203
1
4
11

score 0 · Answer 3 · edited Jul 01 '13 at 14:04

0

Try this:

Dim strval As String = ""
Dim strnum(30) As String

strval = "1,2,3,4,5,6,7,9"

strnum = strval.split(",")

originalEventsToUpdate = (from a in Db.tbl_ffk_event where strnum.contains(a.id) select a).tolist

edited Jul 01 '13 at 14:04

michaelb958--GoFundMonica

4,617
7
31
35

answered Jul 01 '13 at 13:43

ian

1

FYI: This code is VB.NET, not C# as in the question. (Does VB even have LINQ? I honestly don't know.) – michaelb958--GoFundMonica Jul 01 '13 at 14:07
I think they added LINQ to vb on Tuesday – SleepyBoBos Jun 16 '14 at 05:02

Select "IN" in LINQ to SQL

3 Answers3