CODEWITHSUNDEEP
c#.netsqllinqlinq-to-sql
vtortola
vtortola

Reputation: 35963

Select "IN" in LINQ to SQL

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.

Upvotes: 4

Views: 7164

Answers (3)

ian
ian

Reputation: 1

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

Upvotes: 0

Carlos Lemos
Carlos Lemos

Reputation: 203

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

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113392

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?

Upvotes: 2

Related Questions