Thursday, July 10, 2008

Select In Sentence with Linq To Sql

Considering the scenario: If I want to look for some records according to the collection of their ID from the data table, how to write the correct LINQ expression? For instance, I create the training plan table in SQL Server 2005. The data structure of this table is as follows:
ID type: bigint
Name type:nvarchar(50)

Notes: the rest columns have been neglected.

Now I have the collection of ID such as IList which contains some items. It will be the in parameter to find the list of TrainingPlan object. If I use the SQL sentence, it will be:

select * from trainingplan where ID in {1, 2, 3, 4}

So how to do to meet the requirement in LINQ? That’s easy, the code snippet like this:

        public IList GetTrainingPlans(IList ids)


            long[] tpIDs = ids.ToArray();

            var query = from tp in context.TrainingPlans

                        where tpIDs.Contains(tp.ID)

                        select tp;

            return query.ToList();


Please note the first line in the method body. Why did I convert ids(IList) to long[] array? When the input params is IList type, and then invoke its Contains() method, It will throw a NotSupportedException. The error information is “Boolean Contains(System.Int64)” has no supported translation to SQL.

But, if it is Array type or List type, it will not. It’s very strange, isn’t it? Yeah, that’s it whichi I am confused. Maybe the big difference is between the class and interface.

No comments: