Linq to NPersist to MS Access

I’ve finally seen my own Linq support Live :-P
Untill now I’ve only been emitting NPath quereis and verified that NPath is correct.

The reason I havent tested it live untill now is that I only have my gaming machine to develop on atm. the dev machine is dead.

Anyway, I’m trying it against NWind MS Access DB right now and it works like a charm.
I can even run fairly complex queries, even if it takes a bit of time on MS Access.

var res = from cust in ctx.Repository<Customer>() 
          where 
          cust.Orders.Any( 
                 order => order.OrderDetails.Sum( 
                     detail => detail.UnitPrice * detail.Quantity 
                                                ) > 1000) 
          orderby cust.CompanyName 
          select cust;

This lists all customers that have atleast one order with the order total of 1000$.
I was a bit nervous before and thought that maybe it will fail.. even though the NPath looks correct and the NPath to SQL generator have been working fine for the last 3 years ;-)

More Linq support for NPersist

I’ve added a bit more Linq support for NPersist today.

The nice thing is that we can “cheat” in our Linq provider, we can transform our Linq queries into NPath queries.
Thus, I don’t have to touch the wicked SQL generation.
I just have to produce valid NPath, which is pretty similair to Linq.

Just look at the following code:

private string ConvertAnyExpression(MethodCallExpression expression) 
{ 
    string fromWhere = ConvertExpression(expression.Arguments[0]);     
    if (expression.Arguments.Count == 1) 
        return string.Format("(select count(*) from {0}) > 0", fromWhere); 
    else 
    { 
        LambdaExpression lambda = expression.Arguments[1] as LambdaExpression; 
        string anyCond = ConvertExpression(lambda.Body); 
        return string.Format("(select count(*) from {0} and {1}) > 0", fromWhere,anyCond); 
    } 
} 

Thats all the code that we needed in order to support “list.Any()” and “list.Any(x => ….)”
This both makes the Linq provider easier to build, and it makes it way easier to unit test it too.
We simply have to compare the result with an expected NPath string.
Like this:

string expected = "select * from Customer where ((Customer != ?))"; 
string actual = res.Query.ToNPath(); 
Assert.AreEqual<string>(expected, actual);

So I think we have most of the standard query elements in place now.
I just have to add support for a few more things inside NPath, things like “skip”, “is” etc.

Adding Linq support to NPersist

I’m currently adding some real Linq support to NPersist.
I did start on it when the first public previews of Linq was released, but then I was kind of stuck in World of Warcraft for a while (OK, slightly more than a while).
But I’ve finally broken out of the world of Azeroth and I’m back in the real world again.

So, here is a few sneak peeks of what will be possible:

 var res = from cust in ctx.Repository<Customer>() 
          where (from order in cust.Orders 
                 where order.OrderDate == 
                 new DateTime(2008,01,01) && order.Total == 3.1 
                 select order).Count > 0 
          select cust;

 The Linq query will be turned into our own DSL NPath :

select * 
from Customer 
where ((( 
	select count(*) 
	from Orders 
         where ((OrderDate = #2008-01-01#) and (Total = 3.1)) 
        ) > 0))

The NPath query will then be transformed into SQL once the user tries to access the result.
Sure, it is some slight overhead to transform a 2nd query language, but the NPath parser is extremely fast, and NPersist use NPath internally so we just wanted to avoid making two SQL generators.

The SQL generation is by far the biggest beast inside NPersist, it’s big, furry and very angry, so we just want to stay away from it if we can. ;-)

We have also managed to solve the problem with Linq and Load spans that have haunted us since we first saw Linq.
“How the heck do we add load spans to the select when we cannot change the syntax of the select part?”
The solution is quite simple: “You don’t”
Instead, we pass the loadspan to the query source, like this:

var res = from cust in ctx.Repository(new LoadSpan<Customer> 
("Name","Email","Address.StreetName"))    

          select cust; 

This way we do not need to invent weird hacks that don’t really fit into the Linq syntax.
The solution might have been obvious for others, but we were really stuck on it, pretty much because we have always specified load spans in the select clause of NPath.

Making a custom Linq engine

You can download the complete source here
NOTE: The download contains much more than this post.

Today I’m going to show how to make a custom linq engine.

So, why would you want to create your own engine?

Well replicating the standard in mem engine is pretty pointless except for educational purposes.
But you might need custom engines for your own frameworks where you want to query your own special DB or media of some sort.
We use this in NPerist, we transform the linq query into our own NPath DSL and then run the NPath query through NPersist into the database.

Anyway, back to the sample.
Lets start by creating a simple console application and add the following code in the Main method:

using System.Linq; 
... 
... 
... 
//setup test data 
List<string> source = new List<string>() { 
    "public", 
    "protected internal", 
    "void", 
    "static", 
    "for", 
    "string", 
    "int", 
    "long", 
    "List<T>" 
};                  

//run a simple query 
var result = from item in source 
             where item.Length > 1 
             select item + " hello linq"; //display the result                

Console.WriteLine(); 
Console.WriteLine("Result:------------------------"); 
foreach (var item in result) 
{ 
    Console.WriteLine("{0}", item); 
}

Nothing fancy, we simply init a list with a few strings, and then we run a standard linq query on it.

Now lets start writing our own Linq engine.
To do this we need a few delegates:

// T = item type 
// IN = item type IN 
// OUT = item type OUT  
  
//delegate to handle the where clause 
public delegate bool WhereFunc<T>(T type);                

//delegate to handle the select clause 
public delegate OUT SelectFunc<IN, OUT>(IN item);

We also need two extension methods:

namespace MyLinqEngine; 
{ 
public static class Sequence 
{ 
    //extension method that will handle where clauses 
    public static List<T> Where<T> 
    (this List<T> source, WhereFunc<T> matchDelegate) 
    { 
        //create a result list 
        List<T> result = new List<T>();         
        //scan all items in the source 
        foreach (T item in source) 
        { 
            //check for match 
            bool match = matchDelegate(item);             
            if (match) 
            { 
                //add match to result 
                result.Add(item); 
            } 
        } 
   
        return result; 
    }     
    
    //extension method that will handle select clauses 
    public static List<OUT> Select<IN, OUT> 
    (this List<IN> source, SelectFunc<IN, OUT> selectorDelegate ) 
    { 
        //create a result list 
        //since the result will have the exact same size as 
        //the source we can init it with a fixed size 
        List<OUT> result = new List<OUT>(source.Count);                  
        //scan each item in the source 
        foreach (IN item in source) 
        { 
            //transform the items in the source to an 
            //item of the given out type 
            OUT resultItem = selectorDelegate(item); 
            result.Add(resultItem); 
        }         
        return result; 
    } 
} 
}

And that’s it!
That’s all you need in order to create the simplest form of custom Linq engine.

The default Linq extensions are activated by the “using System.Linq;” at the top of your program.cs class file.
We can now remove that line and replace it with “using MyLinqEngine;” in order to activate our own linq extensions.

You’ve now got your very own debuggable Linq engine.

Happy coding.

//Roger

You can download the complete source here
NOTE: The download contains much more than this post.

ORM and C#3 anonymous types

Here goes my first blog post in 1+ years.

Nowadays I’m playing around with VS 2008 trying to abuse all the new features as much as possible.

Earlier today I was talking to Mats Helander about making a NPersist v3, for .NET 3.5.
We were discussing new features such as supporting anonymous types instead of our old “Tabular” concept.

After a while I started to think about the possibilities to use anonymous types to create read only views for stored procs,
and I came up with a quite slick solution ( in my own opinion OFC ;-) )

Anyway, here is what I came up with:

//create a prototype for our result 
var prototype = new { FirstName = "" , Age = 0 , Email = "" };      

//fill a list with items of the above type with data from an sproc 
var res = MyDal.ExecuteStoredProc(prototype, "sp_MyStoredProc", p1, p2, p3);      

//present the data in our top notch UI 
foreach (var item in res) 
{ 
    Console.WriteLine("FirstName = {0}", item.FirstName); 
}

What I do here is:
I create an object of an anonymous type, which will act as a prototype for our result.
The anonymous type contains our properties and the type for each property.

Then I pass the prototype object into my DAL and ask my DAL to execute an stored procedure with some args.
The DAL executes procedure and fill a generic list of my anonymous type with the result.
And then return the filled list the consumer.

This allows the consumer to call a stored procedure and get the result in an objectified way.
Making it possible to work with his data in a typed way w/o adding typed datasets or entity classes.
OK, the anonymous type is a sort of entity class too.
But since it can be designed per use case, you do not have to clutter the project with loads of view specific classes.

Well that’s it for now.
Full sample code will be provided soon.

//Roger