The EntitySpaces Dynamic Query API will soon be sporting Join’s and Arithmetic Expressions. The syntax is so elegant even LINQ enthusiasts will take pause. Let’s take a look at how you would do a join using the enhanced EntitySpaces DynamicQuery. Note that we are using all of your existing objects to build the query.

Performing Joins …

CustomersQuery cust = new CustomersQuery ("c");
OrdersQuery orders = new OrdersQuery ("o");
OrderDetailsQuery details = new OrderDetailsQuery ("d");

cust.Select(cust.ContactName, details.Quantity.Sum().As("TotalQuantity"));
cust.InnerJoin(orders).On(cust.CustomerID == orders.CustomerID);
cust.InnerJoin(details).On(orders.OrderID == details.OrderID);
cust.Where(cust.ContactName.Like("%Mike%"));
cust.GroupBy(cust.ContactName);

CustomersCollection coll = new CustomersCollection ();
coll.Load(cust);    // Load it …

Now that is a pretty sweet syntax …

Of course, RightJoin, LeftJoin, and FullJoin are also supported. The nice thing about this approach is that you are spoonfed the syntax via intellisense, no need to stop and create a view (although you can if you want to and generate your business entities off of the view). The "o", "c" and "c" shown above are merely the aliases used when building the SQL.

Arithmetic Expressions

You can now use arithmetic expressions in your query's. Notice how you can use the natural language syntax with * / + - and %. Take a look at this sample …

CustomersQuery cust = new CustomersQuery ("c");
OrdersQuery orders = new OrdersQuery ("o");
OrderDetailsQuery details = new OrderDetailsQuery ("d");

cust.Select(cust.ContactName, (details.Quantity * details.Price).Sum().As("TotalPrice"));
cust.InnerJoin(orders).On(cust.CustomerID == orders.CustomerID);
cust.InnerJoin(details).On(orders.OrderID == details.OrderID);
cust.Where(cust.ContactName.Like("%Mike%"));

CustomersCollection coll = new CustomersCollection ();
coll.Load(cust);    // Load it …

How Does this Effect Binding?

Your EntitySpaces collections will now provide a new method named LowLevelBind(). Normally, you bind directly to the properties in your EntitySpaces entities. However, when joins are in play you are bringing back columns that aren’t in your entities. Thus, the LowLevelBind will bind directly to the underlying DataTable. The are other ways of course using EntitySpaces but we wont cover those in this post.

grid.DataSource = coll.LowLevelBind();

Can I still Save an Entity that was Built off a Join?

The answer is "yes". Of course, it will only save to the main table, in the above case the Customer table.
We already have this implemented and are hoping to have a beta out with joins and arithmetic expressions around October 1st, 2007.

These new features will be available for all of the many databases supported by EntitySpaces.

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, 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.

The EntitySpaces Team
--

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