Finding data

Dynamic Find methods

The dynamic Table properties exposed by the Database object will accept a variety of methods starting with Find.

• FindBy[…] returns a single dynamic object
• FindAllBy[…] return an IEnumerable<dynamic>.

You can access the dynamic and get resolution at runtime or you can “project” to types you specify:

User u = (User)db.Users.FindById(23);
IEnumerable<User> u = db.Users.FindAllByName("Bob").Cast<User>();
IList<User> u = db.Users.All().ToList<User>();

FindBy

Simple.Data:

db.Users.FindByNameAndPassword(name, password)

SQL:

SELECT * FROM Users WHERE Name = @p1 and Password = @p2

FindAllBy

Simple.Data:

db.Users.FindAllByType(“Admin”)

SQL:

 SELECT * FROM [Users] WHERE [Users].[Type] = @p1

FindAllBy with a list

Simple.Data:

db.Users.FindAllByType(new[] {"Admin",“Owner”,"CoAdmin"})

SQL:

 SELECT * FROM [Users] WHERE [Users].[Type] IN (p1,p2,@p3)

FindAllBy with a Range

Simple.Data:

db.Users.FindAllByJoinDate(“2010-01-01”.to(“2010-12-31”))

SQL:

 SELECT * FROM [Users] WHERE [Users].[JoinDate] BETWEEN @p1 AND @p2

Find and FindAll

More complex criteria can be expressed using the Find and FindAll methods, which accept an expression, kind of similar to LINQ. Except not LINQ.

Simple.Data:

db.Users.Find(db.Users.JoinDate <= “2010-01-01”)

SQL:

SELECT * FROM [Users] WHERE [Users].[JoinDate] <= @p1

Criteria can be combined using the && and || operators. Parentheses for altering grouping of expressions are supported:

Simple.Data:

db.Users.Find(db.Users.Active.Like(“Y”) && db.Users.JoinDate <= “2010-01-01”)

SQL:

SELECT * FROM [Users] WHERE ([Users].[Active] LIKE @p1 AND [Users].[JoinDate]) <= @p2

Supported operators for Find

• ==
• !=
• <
• <=
• >
• >=

Joins

If you have referential integrity set up, Simple.Data will automatically apply joins for Find operations which use sub-object style criteria. Like this:

Simple.Data:

db.Customers.Find(db.Customers.Invoices.Paid == “N”)

SQL:

SELECT [Customers].* FROM [Customers] 

JOIN [Invoices] ON [Customers].[CustomerId] = [Invoices].[CustomerId] 

WHERE [Invoices].[Paid] = @p1

Getting all the data

You can use the All method to return all the data from a table:

Simple.Data:

db.Users.All()

SQL:

SELECT * FROM Users

Like FindAllBy it returns an IEnumerable<dynamic>.