sql和linq转换示例

select id from person where id>1

var data = from person in context.People
            where person.Id > 1
            select new { Id = person.Id };

var data = context.People.Where(person => person.Id > 1).Select(person => new { Id = person.Id });

select id,name from person order by name asc, id desc

var data = from person in context.People
            orderby person.Name ascending, person.Id descending
            select person;

var data = context.People.OrderBy(person => person.Name).ThenByDescending(person => person.Id);

select name from person group by name,age having count(*)>=1 order by count(*) desc

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;

var data = context.People.GroupBy(person => new { person.Name, person.Age }).Where(g => g.Count() >= 1).OrderByDescending(g => g.Count()).Select(g => g.Key);

select a.name,b.name from person a inner join pet b on a.id=b.pid

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 };

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 });

select a.name,isnull(b.name,'-') from person a left join pet b on a.id=b.pid

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 };

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 });