Simple linq queries

Query<T>

NPoco introduces a simple way to fetch an object using LINQ query. Here is a simple example.

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

Visual Studio users: You must have a project reference for System.Data.Linq in order to get Intellisense prompts for the field names when you type x. (using the projection variable) !

The LINQ keywords that are available are:

• ProjectTo
• Count
• Any
• Where
• OrderBy
• OrderByDescending
• ThenBy
• ThenByDescending
• Limit
• Include
• IncludeMany

Here is how you do an IN clause:

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

You can select only part of table columns into anonymous class!

var users = db.Query<User>().Where(x => x.Name.StartsWith("Bo")).ProjectTo(x => new { x.Id, x.Name }).ToList();

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

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

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