Signum Framework Logo
"Open framework that encourages convention over configuration, using C# code,
not XML files, to model at the right level of abstraction and achieve deadlines.
...but also has a full Linq provider, and syncs the schema for you!"



Source code




Take a comfortable seat, breath deeply and prepare yourself. Database.Query<T>() is Signum Engine's gate to the Linq world, a world of elegant compiled-time checked and IntelliSense assisted queries that will empower you to a new level of productivity.

By taking ideas from functional languages (like lambdas and expression trees) with the very practical purpose of integrating queries in the language, Microsoft has completely changed the way enterprise code has to be done. Anyone who has used Linq in some of its' flavours just won't turn back.

A bit of context

The design that Anders Hejlsberg's team did with Linq is really neat. The whole Linq thing is just a bunch of general purpose additions to the language, they didn't hard-code dirty Sql Server code in the C# compiler. Instead they keep the door open to add Linq Providers by implementing the IQueryable interface.

They created two Sql providers. Linq to Sql and Linq to Entities, both encouraging a database-first approach.

There have been some third-party providers since them, most of them IEnumerable providers (just a convenient object model), or IQueryable providers translating to exotic target languages (Linq to Amazon, Linq to SharePoint, Linq to Google Desktop).

However, very few have tried to make a provider for a language as expressive as Sql:

  • George Moudry's DbLinq project is trying to make a Linq to Sql clone for other providers (MySql, Oracle, PostgreeSql) but remains a prototype "capable of simplest queries and updates" and "for most primitive joins".

  • LLBLGen has a decent LINQ provider. The aproximation is quite different since LLBLGen follows a database-first code generation strategy, and we do the oposite. But they also support most of the LINQ operations. Brave guys!

At this moment, and as far as we know, there's no commercial or open source ORM mapper with a full Linq provider apart from Microsoft, LLBLGen and Signum Framework.

Why so many problems with third-party sql providers? They are just really complex to make. But they're worth it: Any query engine previous to Linq is flawed and incomplete compared to the expressiveness given by Linq queries.

Before we start to get into the details just notice that we wouldn't have been able to make any progress without the invaluable help of Matt Warren's tutorials. Thank you Matt :).

Getting started with Linq Queries

Last spring ('08) we tried to use our engine with Microsoft Linq to Sql, but our engine's philosophy didn't play well. We had each entity duplicated so half of our business logic was translating Signum Entities to Linq proxy classes back an forth. Also, as we usually do applications with a lot of entities, we ended up with huge dbml files hard to maintain. Then we took the hard way, building our own Linq provider that speaks straight in terms of our entities.

Finally our shining new Linq provider for Signum Engine is here. It is almost as complete as the Microsoft one. It has some peculiarities though but it's almost the same thing.

Here we are going to focus on these peculiarities, because there are plenty of sites already where you can get an explanation of what Linq is and how the standard operators work. Just to enumerate some of them:

So, at this point, you should be a proficient Linq programmer with experience in some of the pre-Signum Linq flavours, and you're eager to know how our provider works.

Not so fast, first it would be convenient to take a look and get used to the data model we are going to query against in the examples. Take a look.


The first thing to notice is that we didn't generate any db.Bugs property for each table, instead you have to write Database.Query<CustomerDN>() to get the IQueryable<CustomerDN>() to to start querying tlCustomerDN table. You can find out why in main Database page.

Let's see our first example, if you write a query like this:

 var result = from b in Database.Query<BugDN>()
              group b by b.Status into g
              select new { Status = g.Key, Num = g.Count() };

An Sql like this will be sent:

SELECT t1.c0, COUNT(*) AS c01
  (SELECT t0.idStatus AS c0
  FROM tlBugDN AS t0)
) AS t1
GROUP BY t1.c0

Returning the following results:


Retriever Integration

You can mix values, entities, Lazy<T> objects and even MList<T> in your results when calling Linq Queries.

//Mixing an enum, an entity and a MList
var result = from b in Database.Query<BugDN>()
             select new { b.Status, b.Fixer, b.Comments};

The same Retriever class that is internally used for Database.Retrieve() is used in the Linq provider, so each entity is a fully enabled entity with Validation and Serialization support.

This is a really nice feature but it has some performance considerations:
  • Retrieving an entity is heavier than retrieving plain anonymous types because frequently you need to retrieve all the related entities as well. Retrieve the entity only if you need it.
  • And more importantly, if Linq to Signum detects that your query needs a retriever (full entities in the result), it will disable deferred execution. Maybe you won't notice it, but the memory consumption is different.

foreach (var item in Database.Query<BugDN>().Select(a=>a.Description))
    //Low memory consumption, reader is taken one by one

foreach (var item in Database.Query<BugDN>().Select(a => a.Description).ToList()) { //High memory consumption because ToLis() }

foreach (var item in Database.Query<BugDN>().Select(a => a.Fixer)) { //High memory consumption because Fixer is an entity so deferred execution has been turned off }

Dot Join

One of the coolest things about using Linq targeting an Sql Server (apart from being integrated into the language) is this: For most of the joins, those across foreign keys, you don't even have to use a join, you can navigate from one table to the related one just using C# dot (.) operator, when you try to use a field from a entity in a table that is not in the query, an implicit join is done under the covers. Queries become shorter and more readable this way.

            //instead of this 
var query1= from b in Database.Query<BugDN>()
             join d in Database.Query<DeveloperDN>() on b.Fixer.Id equals d.Id
             select new { b.Description, d.Name }; 
            //you can write this
var query2 = from b in Database.Query<BugDN>()
              select new { b.Description, b.Fixer.Name };

There's an exception taking place in query1: To access Fixer.Id we could be doing dot-join. In this case, however, a dot-join is not necessary because the id of the developer is available on tlBugDN column idFixer

However, in Linq to Signum the behaviour of both queries is slightly different. We use outer join for dot joins because we don't want the results to decrease just because you have used a dot join.

-- query1
SELECT t0.Description, t2.Name
FROM tlBugDN AS t0
INNER JOIN tlDeveloperDN AS t2
  ON (t0.idFixer = t2.Id)

-- query2, notice the left outer join SELECT t0.Description, t2.Name FROM tlBugDN AS t0 LEFT OUTER JOIN tlDeveloperDN AS t2 ON (t0.idFixer = t2.Id)

Also, you can imitate inner join behaviour using dot join with this little trick:

var result = from b in Database.Query<BugDN>()
             where b.Fixer != null
             select new { b.Description, b.Fixer.Name }; 

Entity Equality

You can compare two entities using == operator (or the negative version !=) and an automatic identity comparison will be done for you. This is safer than using Ids since you cant get the type wrong.

Also, this identity comparison even works with simple, ImplementedBy and ImplementedByAll references in any combinations so you should use this feature to increase your queries' simplicity and make them more robust against Schema changes.

A query like this:

var result = from b in Database.Query<BugDN>()
             join c in Database.Query<CustomerDN>() on b.Discoverer equals c
             select new { b.Description, c.Name }; 

very similar to the previous example but using ImplementedBy reference Discoverer, will be translated to:

SELECT t0.Description, t2.Name
FROM tlBugDN AS t0
INNER JOIN tlCustomerDN AS t2
  ON (t0.idDiscoverer_CustomerDN = t2.Id)

Returning the following results:

Add ElvesSyndicateDN entitySanta Claus
Support for gifs > $1MillionSanta Claus
Xmas '08 Budget ReportSanta Claus
Massive Destruction flag on WeaponDNACME Industries
Weapons do not workACME Industries
Optional Insurance PolicyACME Industries

If Discoverer would have been decorated with ImplementedByAll attribute instead, the same query would also have worked, being like this:

SELECT t0.Description, t2.Name
FROM tlBugDN AS t0
INNER JOIN tlCustomerDN AS t2
  ON ((t2.Id = t0.idDiscoverer) AND (3 = t0.idDiscoverer_Tipo))

There are more complex cases, comparing an arbitrary ImplementedBy reference against a ImplementedByAll reference would be pain in the neck if done manually, so use this feature! It saves you problems and you will do safer code.

Finally, just notice that you can also use this method with local entity objects that are used as a parameter in your queries. For example, the following code:

DeveloperDN antonio = Database.Query<DeveloperDN>().Single(a => a.Name == "Antonio");

var result = from b in Database.Query<BugDN>() where b.Fixer == antonio select new { b.Description, b.Hours };

Executes exactly the following Sql commands:

-- Look for Antonio Id
FROM tlDeveloperDN AS t0
WHERE (t0.Name = 'Antonio')
-- Retrieve Antonio (in a local variable). Look at the Query - Retriver integration by the way. 
SELECT * FROM tlDeveloperDN WHERE Id IN (4)
-- Look for Bugs created  by Antonio. 
SELECT t0.Description, t0.Hours
FROM tlBugDN AS t0
WHERE (t0.idFixer = 4)

For the sake of simplicity I'm removing SqlParameters in the Sql examples, but don't worry, you are 100% safe from sql injection attacks while using Linq to Signum


One difference (another one!) between the relational world and the OOP is that object references have a very clear direction (they are just pointers) while foreign keys are kind of bidirectional.

Other providers (like Like to Sql) solve this creating two properties instead of one. In our examples it would be like having a Bugs property on ProjectDN for example.

This solution is nice, but has some drawbacks for us:

  • It is impossible using Signum philosophy because we don't generate the code, you write it instead.
  • We design entities to live in the client application as well, these properties won't work in that case.
  • Doesn't play nicely with SchemaBuilderSettings flexibility to connect entities without actually touching them.
  • Also, and this has nothing to do with Signum philosophy, sometimes the name of these properties are long. i.e.: DeveloperDN.BugsIAmDiscoverer, DeveloperDN.BugsIAmFixer

Our solution, instead, is the Database Back functions. It's defined like this.

 public static IQueryable<T> Back<T, S>(this S sourceElement, Expression<Func<T, S>> route)
        where T : IdentifiableEntity
        where S : IdentifiableEntity

Don't worry, you don't have to look at the definition above until you eyes fall out. It's easier with an example:

DeveloperDN antonio = Database.Query<DeveloperDN>().Single(a => a.Name == "Antonio");

//In the previous example we did something like that var result1 = Database.Query<BugDN>().Where(b=>b.Fixer == antonio);

//With back function we could get something like this instead var result2 = antonio.Back<BugDN, DeveloperDN>(b => b.Fixer);

//Or, pushing type inference by using an explicit typed in the lambda, the much nicer var result3 = antonio.Back((BugDN b) => b.Fixer);

Some people prefer the first code, others the last. It's up to you man. We just feel we had to cover the back references hole.

Finally, there's another overload of back that accepts a IEnumerable<S> as the result of the lambda, so it will work if BigDN have a MList<DeveloperDN> fixers field:

var result3  = antonio.Back((BugDN b) => b.Fixers);

Queries and Lazy<T>

Lazy<T> is fully supported in queries. If you don't know what a Lazy<T> is, take a look here.

There are two possible uses of Lazy objects:

Navigate through Lazy objects: At the database level there's just no difference between a Lazy reference an a normal one. Nothing. Zero. Lazy objects are just a convenient way to tell the retriever when to finish (to avoid retrieving the whole database) and to delimiter the graph that will be sent to the client application.

When you are writing a Linq to Signum query, however, you have to pretend you are diving in the depth of your database.


In there, all the entities are available so you don't have to retrieve them , and there's just one instance of each entity so you can use == operator to test if they are equal.

Having said that, just use EntityOrNull property to jump over Lazy entities when you are in a Linq to Signum query, if the lazy is not null, there will always be an entity at the other side.

var result = from b in Database.Query<BugDN>()
             select new {b.Description, Project = b.Project.EntityOrNull.Name}

Use Lazy objects in the results: The second use of Lazy objects is to get them out from the database using a Linq query. You can create Lazy objects explicitly using {ToLazy extension method on any entity, or just get them if they are already in your data model:

//The first property is an explicit lazy, while the second is lazy just because BugDN.Project is Lazy. 
var result = from b in Database.Query<BugDN>()
             select new { Bug = b.ToLazy() , Project = b.Project}; 

The result of the previous query is really performant:

SELECT t0.Id, t0.ToStr, t0.idProject, r0.ToStr AS ToStr1
FROM tlBugDN AS t0
  ON (t0.idProject = r0.Id)

As you see, it retrieves all the entities on Lazy objects on the query itself, without using the retriever.

This behaviour, however, can not be extended to lazy ImplementedBy and ImplementedByAll references because the ToStr field will be in different tables:

var result = from b in Database.Query<BugDN>()
             select b.Discoverer.ToLazy(); 

The code uses the retriever in this case, so is slightly slower:

SELECT t0.idDiscoverer_CustomerDN, t0.idDiscoverer_DeveloperDN
FROM tlBugDN AS t0

SELECT Id, ToStr FROM tlDeveloperDN WHERE Id IN (2, 3, 1)

SELECT Id, ToStr FROM tlCustomerDN WHERE Id IN (1, 2)

A point about Lazy Retrieving: It's not the same being a ImplementedBy/ImplementedByAll lazy reference as having Lazy's Type (T) and RuntimeType de-synchronized. You could upcast a bug lazy doing bug.ToLazy<Entity>() and you still have the benefits of the optimization.

SelectMany and Join differences

It's too long to explan this here, so we have moved it to another page.

Cast/Is/As and Queries

For basic types, cast are just ignored by the translation pipeline. There's no point to translate them because:

  • Implicit conversion done by Sql usually does the work.
  • When the data is retrieved a Convert.ChangeType is done on every data retrieved that does not match the required type.

When dealing with polymorphic foreign keys, however, down-casting is really helpful: When you use casting in an expression over a field that is marked as ImplementedBy or ImplementedByAll (even using SchemaBuilderSettings), a convenient dot join will be done any time you try to access a member of this casted expression.

var result = from b in Database.Query<BugDN>()
             select new { b.Description, ((DeveloperDN)b.Discoverer).Name };


SELECT t0.Description, t2.Name
FROM tlBugDN AS t0
  ON (t0.idDiscoverer_DeveloperDN = t2.Id)

And returning:

MySql SupportAlejandro
Enum CastingLuís
SchemaBuilderSettings supportAlejandro
FileLine with LazyOlmo
Add ElvesSyndicateDN entity
Support for gifs > $1Million
Xmas '08 Budget Report
Translate some windows to QuenyaLuís
Massive Destruction flag on WeaponDN
Weapons do not work
Optional Insurance Policy

It is very convenient, isn't it?

The only difference between casting and the as operator is that, when failing, one throws an exception while the other returns null. It won't make sense to throw exceptions inside of an Sql query so there's no difference between using as operator or normal casting over your ImplementedBy or ImplementedByAll fields.

Is Operator is used to test if an object has a type, returning true if so, and false otherwise. This operator is also supported by Linq to Signum when used against ImplementedBy or ImplementedByAll fields.

var result = from b in Database.Query<BugDN>()
             where b.Discoverer is DeveloperDN
             select new { b.Description, ((DeveloperDN)b.Discoverer).Name };

Generating Sql:

SELECT t2.Description, t3.Name
  (SELECT t0.Description, t0.idDiscoverer_DeveloperDN
  FROM tlBugDN AS t0
  WHERE  NOT (t0.idDiscoverer_DeveloperDN IS NULL ))
) AS t2
  ON (t2.idDiscoverer_DeveloperDN = t3.Id)

And returning:

MySql SupportAlejandro
Enum CastingLuís
SchemaBuilderSettings supportAlejandro
FileLine with LazyOlmo
Translate some windows to QuenyaLuís

There's one important difference when using is operator on a query: It tests for concrete type, not for interface implementation or class inheritance. i.e.: Testing myLion is Animal will return false.


GroupBy behaves in more or less the same way as it does in Linq to Sql. Be careful, however, that if you are retriving hierarchical data (not aggregated data) a Sql for each group will be sent, so it could have some performance problems.

For example:

var result = from b in Database.Query<BugDN>()
             group b by b.Status into g
             select new { Status = g.Key, Num = g.Count(), Avg = g.Average(e => e.Hours) };

Is converted in just this sql,

SELECT t1.c0, COUNT(*) AS c01, AVG(t1.Hours) AS c1
  (SELECT t0.Hours, t0.idStatus AS c0
  FROM tlBugDN AS t0)
) AS t1
GROUP BY t1.c0

retriving this data:


But if you write this instead:

var result = from b in Database.Query<BugDN>()
             group b.Description by b.Status into g
             select new { Status = g.Key, Num = g.ToList().ToString(", ") };

The generated sql will be that long, and the more groups, the more Sqls, so be careful!:

SELECT t1.c0, t1.c0 AS c01, @p0 AS c02
  (SELECT t0.idStatus AS c0
  FROM tlBugDN AS t0)
) AS t1
GROUP BY t1.c0
@p0 NVarChar: ", "

SELECT r1.Description FROM ( (SELECT r0.Description, r0.idStatus AS c0 FROM tlBugDN AS r0) ) AS r1 WHERE (@p0 = r1.c0) @p0 Int: Playground.Status.Open

SELECT r1.Description FROM ( (SELECT r0.Description, r0.idStatus AS c0 FROM tlBugDN AS r0) ) AS r1 WHERE (@p0 = r1.c0) @p0 Int: Playground.Status.Fixed

SELECT r1.Description FROM ( (SELECT r0.Description, r0.idStatus AS c0 FROM tlBugDN AS r0) ) AS r1 WHERE (@p0 = r1.c0) @p0 Int: Playground.Status.Rejected

OpenEnum Casting
FixedSchemaBuilderSettings support, FileLine with Lazy, Add ElvesSyndicateDN entity, Support for gifs > $1Million, Massive Destruction flag on WeaponDN, O
RejectedMySql Support, Xmas '08 Budget Report, Translate some windows to Quenya, Weapons do not work

IQToolkit has more clever implementations for this pattern, maybe we will integrate something like this in the future.

In the C# example you can see an odd ToString(", ") method, it forks over any IEnumerable<T> and it's deffined in Signum.Utilities, so the preceding ToList() is not really necessary. Maybe, however, we will add native support of ToString(string separator) on Linq to Signum using FOR XML PATH (or something like this) in the future.

Where the code gets executed

On Linq queries based on expression trees, is up to the provider to decide what parts of the query will be executed in C# or SQL. Usually is a mixture of both:

  • If the expression is going to be evaluated 'at the end' of the query, most of it is executed in C# code.
  • If the expression is part of a where condition, a group by or something like this, then it will need to be completely translated to sql. If not possible an exception is thrown.

So if you define a local function that is not known (hard coded) by Linq to Signum:

public static string ToPascalCase(string text)

You can still write something like this:

var result = from b in Database.Query<BugDN>()
             select ToPascalCase(b.Description);

And it will generate an Sql just like:

SELECT t0.Description FROM tlBugDN AS t0

Executing your function once the data is retrieved.

If you try something like this, however:

var result = from b in Database.Query<BugDN>()
             where ToPascalCase(b.Description) == "Hi"
             select b.Description;

You will get an exception like this:

The expression can't be translated to SQL: (ToPascalCase(1001) = "Hi")

because there's no possible way for Sql to know what your function does (and it's not possible to send a C# delegate in a SqlCommand). We call this pure-sql expression.

There's a lot of expressiveness, though, using only pure-sql expressions. Take a look at the following functions:

Sql Functions

There are some .Net functions that are natively supported by Linq to Signum and will be translated to Sql equivalents, so you have compile-time checking and you can forget about what the name of the function in Sql was. The list:


.Net FunctionSql function
a.IndexOf(b)CHARINDEX(b, a) -1
a.IndexOf(b, i)CHARINDEX(b, a, i + 1 ) - 1
a.Replace(b,c)REPLACE(a, b, c)
a.Substring(i,j)SUBSTRING(a, i + 1, j)
a.Contains(b)a LIKE '%' + b + '%'
a.StartWith(b)a LIKE b + '%'
a.EndsWith(b)a LIKE '%' + b
a.Left(i)*LEFT( a, i)
a.Right(i)*RIGHT( a, i)
a.Replicate(n)*REPLICATE( a, n )
a.Like(b)*a LIKE b

* Extension methods defined in Signum.Utilities. They work on .Net code too.


.Net FunctionSql function
a.DayOfYearDATEPART(dayofyear, a)
a.HourDATEPART(hour, a)
a.MinuteDATEPART(minute, a)
a.SecondDATEPART(second, a)
a.MillisecondDATEPART(millisecond, a)
a.AddDays(i)DATEPART(day, i, a)
a.AddHours(i)DATEPART(hout, i, a)
a.AddMilliseconds(i)DATEPART(millisecond, i, a)
a.AddMinutes(i)DATEPART(minute, i, a)
a.AddMonths(i)DATEPART(month, i, a)
a.AddSeconds(i)DATEPART(second, i, a)
a.AddYears(i)DATEPART(year, i, a)

Also, a.Date is translated as:

DATEADD(hour, - DATEPART(hour, a),
DATEADD(minute, -DATEPART(minute, a),
DATEADD(second, - DATEPART(second, a),
DATEADD(millisecond, - DATEPART(millisecond, a), a))))


.Net FunctionSql function
(a-b).TotalDaysDATEDIFF(day, b, a)
(a-b).TotalHoursDATEDIFF(hour, b, a)
(a-b).TotalMillisecondsDATEDIFF(millisecond, b, a)
(a-b).TotalSecondsDATEDIFF(second, b, a)
(a-b).TotalMinutesDATEDIFF(minute, b, a)


.Net FunctionSql function

Sql Compatibility Extensions

Using sql views to retrieve data has some big disadvantages:

  • Lack of flexibility with Schema changes (ImplementedBy, ImplementedByAll)
  • Lack of all the nice features of Linq to Signum, like retriever integration, retrying typed lazy objects, enums, hierarchical data...

But, in our opinion, the worst one is that sql views do not compile. Neither refactorings are easy on them.

That means that, while building your application, every sql view you create is like adding a layer of spider web over your entities. Once you have created enough views your entities get stuck and just changing the name of a entity field (property, column) is a whole project to accomplish.

Try to avoid (or delay at least) anything that will make changing your entities too hard, because they are in the centre of your application and still are going to be the target of most of your customer's changes.

Having said that, there are situations where you Need to access a custom view in your Linq queries. i.e.: Schema Synchronizer use this feature to access Information Schema views that can't be accessed in any other way.

This is how you can do it:

  • Create a 'chassis' class that represents the view structure.
    • Make the class implement IView (just a marker interface)
    • Uses the view name as the name of the class or, if not possible, use SqlViewNameAttribute instead.
    • Add a public field for each column you are interested in, with a compatible type and the same name as the column, if not possible, use SqlViewColumnAttribute instead.

Example, information_schema.tables system view will look like this:

    internal class SchemaTables : IView
        public string TABLE_CATALOG;
        public string TABLE_SCHEMA;
        public string TABLE_NAME;
        public string TABLE_TYPE;


You don't need (really, you can't) add views to the Schema, but you can mix Database.View<T>() and Database.Query<T>() in the same query.

This technique for views is also valid to access tables not controlled by Signum Engine.

Finally, you can access store procedures in your queries by creating similar 'chassis' methods. For example, you need to call OBJECT_ID function like this:

 public static class SqlMethods
     public static int Object_id(string name)
         return 0; //really, it's never going to be executed

The attribute is enough for Linq to Signum to know that it's a chassis method. You can override the methods' name using SqlMethodAttribute parameter.

Creative Commons License Signum Framework Site by Signum Software is licensed under a Creative Commons Attribution 3.0 License.
Powered by ScrewTurn Wiki version