linq “left join” 用法

2011-12-30

linq “left join” 用法求助我现有project和members两个个表project(prono（主键）, prolevel, company,...)m

linq “left join” 用法求助
我现有project和members两个个表
project(prono（主键）, prolevel, company, ...)
members(prono,authid,roleid（3个字段联合主键）authname ...)

现在想把以下SQL语句实现的结果
select a.*,b.* from
(
select distinct prono,authid from dbo.mumbers where authid='12345'
) a left join dbo.project b on a.prono=b.prono

用linq 来实现，写成一个方法，根据传入的authid返回一个datatable该怎么写？

public DataTable GetProVyAuth(string sAuthID)
{
DataClassesDataContext db = new DataClassesDataContext();
.......

}

[解决办法]
给你一个参考：
var sqlresult = from b in book
join s in students
on b.borroeStudentNo equals s.No
into bs
from bb in bs.DefaultIfEmpty()
select new { b.bookName,
SNo = s==null?"":s.No,
SName = s==null?"":s.Name
};
[解决办法]
做链接其实就是 SelectMany 操作。
[解决办法]
做个例子：

SELECT dbo.vitem.Iid, dbo.vitem.Vid, dbo.vitem.Itemtext, ISNULL(dbo.ve_cote_c.num, 0) AS num
FROM dbo.vitem LEFT OUTER JOIN

dbo.ve_cote_c ON dbo.vitem.Iid = dbo.ve_cote_c.Iid
WHERE (dbo.vitem.Itemtext IS NOT NULL) AND (dbo.vitem.flag = 0)

这个上面的是数据库的操作，把2表用视图连接起来

public object detiles_c(int vid,out int vcount)
{

var q = (from d in vd.ve_vote_d
where d.Vid == vid
select d.num).Sum();
vcount = Convert.ToInt32(q);
var cs = from c in vd.ve_vote_d
where c.Vid == vid
select new
{
c.Iid,
c.Itemtext,

c.num,

c.Vid,
};
return cs;
}

上面的方法是BLL层的Linq 写法

下来你只需要调用就Ok

[解决办法]

C# code

/ 表示产品类别的集合        static IList<Catetory> cateList = new List<Catetory> { new Catetory{CategoryId=1, CategoryName="Fruit"},new Catetory{CategoryId=2, CategoryName="Mobile"}, new Catetory{CategoryId=3, CategoryName="Software"}};        // 表示产品的集合        static IList<Product> prodList = new List<Product> { new Product{CategoryId=1, Name="Banana", ProductId= 1},new Product{CategoryId=1, ProductId=2, Name="Apple"}, new Product{CategoryId=1, Name="Pear" , ProductId=3},new Product{CategoryId=2, ProductId=4, Name="NOKIA"}, new Product{CategoryId=2, ProductId=5 , Name="iPhone"},new Product{CategoryId=3, Name="Office", ProductId=6}, new Product{CategoryId=3, ProductId=7,Name="Sql SERVER"},new Product{CategoryId=4, Name="Hardware" ,ProductId=8}};// 下面的查询联接产品、产品类别两个集合。用CategoryId做为键进行联接，把所有的键值相等的两个集合的对象            // 找出来。形成一个新的对象。这里使用的是查询表达式语法。            var prdli = from prod in prodList                        join cate in cateList on prod.CategoryId equals cate.CategoryId                        select new                        {                            CategoryName = cate.CategoryName,                            ProductName = prod.Name,                            ProdctId = prod.ProductId                        };

热点排行

.NET

linq “left join” 用法