Linq Support

Object definition:

public class Person : DbObjectModel<Person>
{
    [DbColumn("Name")]
    public string FirstName { get; set; }
}

Operation code:

var list = from p in Person.Table
    where p.FirstName.StartsWith("T") && (p.Id >= 1 || p.Id == 15)
    order by p.Id select p;

foreach(Person p in list)
{
    Console.WriteLine(p);
}

We can also operate it as following:

var list = DbEntry.From<Person>()
    .Where(p => p.FirstName.StartsWith("T") && (p.Id >= 1 || p.Id == 15))
    .OrderBy(p => p.Id)
    .Select();

foreach(Person p in list)
{
    Console.WriteLine(p);
}

The composed SQL just like:

Select [Id],[Name] From [Person] Where ([Name] Like 'T%') And (([Id] >= 1) Or ([Id] = 15)) Order By [Id] ASC;

Or we can use the new style condition and old style order by for query:

var list = DbEntry.From<Person>()
    .Where(p => p.FirstName.StartsWith("T") && (p.Id >= 1 || p.Id == 15))
    .OrderBy("Id DESC")
    .Select();

And select new expression of linq also supported:

var list = from p in Person.Table
    where p.FirstName.StartsWith("T") && (p.Id >= 1 || p.Id == 15)
    order by p.Id select new { p.Name };

With it, we have complie time type check of the sytax like condition, order by etc. And it will provide us intellisense of the fields in IDE.

And we can use it in bussiness layer like:

public Person FindPerson(string name, string password)
{
    var p = Person.FindOne(p => p.Name == name && p.Password == password);
    return p;
}

We can use In function in Linq as well:

Person.Find(p => p.Id.In(1, 3, 5));

And use SQL in InSql function:

Person.Find(p => p.Id.InSql("Select [UID] From Others"));

In most cases we might want to use SqlStatement in InStatement function as:

var smt = DbEntry.From<PCs>().Where(p => p.Id >= 2).GetStatement(p => p.Id);
var list = DbEntry.From<Person>().Where(CK.K["Id"].InStatement(smt)).OrderBy(p => p.Id).Select();

There are NotInXXX functions as well.

ConditionBuilder could help us to build condition with compile time error check:

var builder = new ConditionBuilder<Person>();
builder &= p => p.Id == 2;
var condition = builder.ToCondition();
var list = Person.Find(condition);

Last edited Apr 9, 2014 at 10:45 AM by lifeng, version 23

Comments

garfieldzf Oct 31, 2012 at 9:23 AM 
linq samples are too little