This little gem was brought up by one of our customers, Paul_IBSoftware, and it looked like fun to implement so we snuck it into our Q3 release. Nothing like getting a suggestion and having it in the product the same day. Basically, we had the SQL conjuctions AND and OR but we didn’t have anyway to indicate AND NOT or OR NOT but that is no longer true, see the example below.

VB.NET

Dim q As New EmployeesQuery()
q.es.Top = 1
q.Where(q.EmployeeID = 1 And Not (q.LastName = "goofy"))
q.Load()

Notice you can now use And Not/Or Not in your query syntax. This will yield the following SQL syntax.

SELECT  TOP 1 *
FROM [Employees]
WHERE ([EmployeeID] = @EmployeeID1 AND NOT [LastName] = @LastName2)

C#

You can do this in C# too, however this is one case where the VB.NET syntax reads better than the C# syntax. Take a look at the sample below.

EmployeesQuery q = new EmployeesQuery();
q.es.Top = 1;
q.Where(q.EmployeeID == 1 && ! (q.LastName == "googy"));
q.Load();

Granted, a C# developer would probably never think of that syntax, but it yields the exact same SQL as above. Probably a C# developer would write it as below

EmployeesQuery q = new EmployeesQuery();
q.es.Top = 1;
q.Where(q.EmployeeID == 1 && (q.LastName != "googy"));
q.Load();

The above query would product the following SQL.

SELECT  TOP 1 *
FROM [Employees]
WHERE ([EmployeeID] = @EmployeeID1 AND [LastName] <> @LastName2)

Summary

The EntitySpaces 2009 Q3 DynamicQuery is going to blow your socks off. We are so excited about this release, things are moving very quickly too.

From Mobile Devices to large scale enterprise solutions in need of serious transaction support, EntitySpaces can meet your needs. Whether you’re writing an ASP.NET application with Medium Trust requirements, a Silverlight application, a Mono application, or a Windows.Forms application, the EntitySpaces architecture is there for you. EntitySpaces is provider independent, which means that you can run the same binary code against any of the supported databases. EntitySpaces is available in both C# and VB.NET. EntitySpaces uses no reflection, no XML files, and sports a tiny foot print of less than 200k. Pound for pound, EntitySpaces is one tough, dependable .NET architecture.

EntitySpaces LLC
Persistence Layer and Business Objects for Microsoft .NET
http://www.entityspaces.net