实体 Famework 中的 where in 子句

Question

我需要编写一个带有“where in”子句的查询。我正在使用实体框架 4。

我的 sql 查询是：

select ITEMNMBR, locncode, qtyonhnd, atyalloc 
from dbo.iv00102 
where ITEMNMBR IN (
                   SELECT  cmptitnm 
                   from dbo.bm00111
                   where itemnmbr == bomItem)
AND LOCNCODE = 'MEMPHIS'

需要这样的查询：

public static Func<DBEntities, string, IQueryable<IV00102>> compiledMemphisQuery =
        CompiledQuery.Compile((DBEntities ctx, string bomNumber) =>
            from items in ctx.IV00102
            where   items.ITEMNMBR in (
                                        from orders in ctx.bm00111
                                        where orders.itemnmbr == bomItem
                                        select orders.cmpitnm)
            and items.locncode == "Memphis"
            select items);

Answer 1

使用查询的 Contains 扩展方法。它应该有效：

public static Func<DBEntities, string, IQueryable<IV00102>> compiledMemphisQuery =
    CompiledQuery.Compile((DBEntities ctx, string bomNumber) =>
        from items in ctx.IV00102
        where   (
                                    from orders in ctx.bm00111
                                    where orders.itemnmbr == bomItem
                                    select orders.cmpitnm)
        and items.locncode == "Memphis"
        select items).Contains(items.ITEMNMBR);