sql 中 in 在 Linq to sql 中的使用

    这是大家平时会写到的sql 代码，可能转到 linq 以后很多人不知道怎么来写了。

SELECT * FROM Product WHERE ProductID IN ()

    这个可以这样写：

int[] productList = new int[]{ 1, 2, 3, 4 };

var myProducts = from p in db.Products
                 where productList.Contains(p.ProductID)
                 select p;

    注意顺序。sql诸多限制确实是很烦人，比如这里的数组一定不要超过2100条，不然就会出错，如果使用上面的方法出现一些错误的话，估计就是数据量太大了（productList 超过2100条）。希望在数据量大的时候尽量用其他合适的方法解决，以免带来不必要的麻烦，具体sql server的限制可以看看下面这个表格：

SQL Server 2005 Database Engine object	Maximum sizes/numbers SQL Server 2005 (32-bit)	Maximum sizes/numbers SQL Server 2005 (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8,000	8,000
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8,060	8,060
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per base table	1,024	1,024
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	1,024	1,024
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,258 terabytes	524,258 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server 2005 editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances. SQL Server 2005 supports 25 instances on a failover cluster.	50 instances on a stand-alone server. 25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	249	249
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1,000	1,000
Statistics on non-indexed columns	2,000	2,000
Tables per SELECT statement	256	256
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered
User connections	32,767	32,767
XML indexes	249	249