Simple linq queries

FetchBy

Starting in v2.0 NPoco introduces a simple way to fetch an object using LINQ to filter, order and page. Here is a simple example.

IDatabase db = new Database("connString");
db.FetchBy<User>(sql => sql.Where(x => x.Name == "Bob")
                           .OrderBy(x => x.UserId)
                           .Limit(10, 10))

The LINQ keywords that are available are:

• Select
• Where
• OrderBy
• OrderByDescending
• ThenBy
• ThenByDescending
• Limit
• GroupBy
• Having

Here is how you do an IN clause:

var users = db.FetchBy<User>(sql => sql.Where(x => new[] {1,2,3,4}.Contains(x.UserId)));
// or using the 'In' extension method
var users = db.FetchBy<User>(sql => sql.Where(x => x.UserId.In(new[] {1,2,3,4})));

There are also a number of string methods that can be used in the where clause. Here are a few examples:

var users = db.FetchBy<User>(sql => sql.Where(x => x.Name.StartsWith("Bo")));
var users = db.FetchBy<User>(sql => sql.Where(x => x.Name.EndsWith("ob")));
var users = db.FetchBy<User>(sql => sql.Where(x => x.Name.Contains("o")));
var users = db.FetchBy<User>(sql => sql.Where(x => x.Name.ToLower() == "bob"));
var users = db.FetchBy<User>(sql => sql.Where(x => x.Name.ToUpper() == "BOB"));

Note. Not all operators have been implemented so if you find one that is not but should be, please create an issue.

FetchWhere

There is also a shortcut method if you just need to do a where clause. Here is a quick example of it.

var users = db.FetchWhere<User>(x => x.UserId == 2 && x.IsMale);