表结构如下
直接写表达式
// 正常选取 var user = context.Users.Where(p => p.UserID == 10300 ).Select(p => new {p, p.UserTags} ) // 带条件选取 var user = context.Users.Where(p => p.UserID == 10300 ).Select(p => new {p, UserTags = p.UserTags.Where(o => o.TagID > 10)} ) SELECT [ t0 ] . [ UserID ] , [ t0 ] . [ Email ] , [ t0 ] . [ NickName ] , [ t1 ] . [ UserID ] AS [ UserID2 ] , [ t1 ] . [ TagID ] , ( SELECT COUNT ( * ) FROM [ dbo ] . [ UserTag ] AS [ t2 ] WHERE [ t2 ] . [ UserID ] = [ t0 ] . [ UserID ] ) AS [ value ] FROM [ dbo ] . [ Users ] AS [ t0 ] LEFT OUTER JOIN [ dbo ] . [ UserTag ] AS [ t1 ] ON [ t1 ] . [ UserID ] = [ t0 ] . [ UserID ] WHERE [ t0 ] . [ UserID ] = 10300 ORDER BY [ t0 ] . [ UserID ] , [ t1 ] . [ TagID ]使用LoadOption选项
var option = new DataLoadOptions();option.AssociateWith<User>(p => p.UserTags.Where(o => o.TagID > 10)); context.LoadOptions = option;var user = context.Users.Where(p => p.UserID == 10300 ).Single();var userTags = user.UserTags;这里要把LoadWith改成AssociateWith,因为LoadWith后面的参数只能以*.*这种形式出现,说白了就是只能是一整条记录,而且不能加过滤任何条件
使用Join语句
使用方法也和之前的一样,另外我们还可以用Contains()来实现同样的效果,翻译成的SQL语句也会相应的变成EXISTS 或者 IN. 总结 总的来说,我还是觉得如果要进行多表操作的话Join是首选。至于LoadOption,我只能感慨“既生Join,何生LoadOption”,当然如果LoadOption还有隐藏必杀技的话,希望知道的朋友可以告诉我。 对于1:M或者M:M的关系,不论用上面哪种方法,都会选出一堆重复数据,如果我不想要那些重复数据怎么办?对于1:M关系的数据还好说,直接取2次就成了,但是对于M:M的呢?我现在的做法是第一次取了之后,把参数转成字符串,然后再到SQL里面去Split(用CLR函数),不知道还有没有更好的办法?
转载于:https://www.cnblogs.com/yzlhccdec/archive/2008/03/11/1100928.html