木子屋
 

标题: sql和linq转换示例

作者: dnawo

日期: 2013-02-22 09:16 PM

分类: Win编程

内容: 1.查询
sql：
复制内容到剪贴板 程序代码select id from person where id>1
linq(查询语法)：
复制内容到剪贴板 程序代码var data = from person in context.People
            where person.Id > 1
            select new { Id = person.Id };
linq(方法语法)：  
复制内容到剪贴板 程序代码var data = context.People.Where(person => person.Id > 1).Select(person => new { Id = person.Id });
2.排序
sql：
复制内容到剪贴板 程序代码select id,name from person order by name asc, id desc
linq(查询语法)：
复制内容到剪贴板 程序代码var data = from person in context.People
            orderby person.Name ascending, person.Id descending
            select person;
linq(方法语法)：
复制内容到剪贴板 程序代码var data = context.People.OrderBy(person => person.Name).ThenByDescending(person => person.Id);
3.分组
sql：
复制内容到剪贴板 程序代码select name from person group by name,age having count(*)>=1 order by count(*) desc
linq(查询语法)：
复制内容到剪贴板 程序代码var data = from person in context.People
            group person by new { person.Name, person.Age } into g
            where g.Count() >= 1
            orderby g.Count() descending
            select g.Key;
linq(方法语法)：
复制内容到剪贴板 程序代码var data = context.People.GroupBy(person => new { person.Name, person.Age }).Where(g => g.Count() >= 1).OrderByDescending(g => g.Count()).Select(g => g.Key);
4.连接
sql：
复制内容到剪贴板 程序代码select a.name,b.name from person a inner join pet b on a.id=b.pid
linq(查询语法)：
复制内容到剪贴板 程序代码var data = from person in context.People
            join pet in context.Pets on person.Id equals pet.Pid
            select new { Name1 = person.Name, Name2 = pet.Name };
//or
var data = from person in context.People
            let pets = person.Pets
            from pet in pets
            select new { Name1 = person.Name, Name2 = pet.Name };
linq(方法语法)：
复制内容到剪贴板 程序代码var data = context.People.Join(context.Pets, person => person.Id, pet => pet.Pid, (person, pet) => new { Name1 = person.Name, Name2 = pet.Name });
//or
var data = context.People.SelectMany(person => person.Pets, (person, pet) => new { Name1 = person.Name, Name2 = pet.Name });
5.左连接
sql：
复制内容到剪贴板 程序代码select a.name,isnull(b.name,'-') from person a left join pet b on a.id=b.pid
linq(查询语法)：
复制内容到剪贴板 程序代码var data = from person in context.People
            join pet in context.Pets on person.Id equals pet.Pid into pets
            from item in pets.DefaultIfEmpty()
            select new { Name1 = person.Name, Name2 = item == null ? "-" : item.Name };
linq(方法语法)：
复制内容到剪贴板 程序代码var data = context.People.GroupJoin(context.Pets, person => person.Id, pet => pet.Pid, (person, pets) => new { person = person, pets = pets }).SelectMany(item => item.pets.DefaultIfEmpty(), (item, pet) => new { Name1 = item.person.Name, Name2 = pet == null ? "-" : pet.Name });

+ 查看当前日志评论 (0)

 

登录

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.047 ms

标题: sql和linq转换示例

你没有权限发表评论

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.047 ms

暂无评论

返回

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.047 ms