Understanding ASP NET ORM 101

LINQ stands for language integrated query. It allows you to use "SQL style" query language directly within C# to extract information from data sources.

• That data source could be a SQL server database - this is Linq to SQL
• That data source could be an data context of entity framework objects - Linq to entities.
• That data source could be ADO.net data sets - Linq to Dataset.

That data source could also be an XML file - Linq to XML.
Or even just a Collection class of plain objects - Linq to Objects.

LINQ describes the querying technology, the rest of the name describes the source of the data being queried.

For a bit of extra background:

Datasets are ADO.net objects where data is loaded from a database into a .net Dataset and Linq can be used to query that data after it's loaded.

With Linq to SQL you define .net classes that map to the database and Linq-to-SQL takes care of loading the data from the SQL server database

And finally the Entity framework is a system where you can define a database and object mapping in XML, and can then use Linq to query the data that is loaded via this mapping.

And also

• all of them are LINQ - Language Integrated Query - so they all share a lot of commonality. All these "dialects" basically allow you to do a query-style select of data, from various sources.

• Linq-to-SQL is Microsoft's first attempt at an ORM - Object-Relational Mapper. It supports SQL Server only. It's a mapping technology to map SQL Server database tables to .NET objects.

• Linq-to-Entities is the same idea, but using Entity Framework in the background, as the ORM - again from Microsoft, but supporting multiple database backends

• Linq-to-DataSets is LINQ, but using is against the "old-style" ADO.NET 2.0 DataSets - in the times before ORM's from Microsoft, all you could do with ADO.NET was returning DataSets, DataTables etc., and Linq-to-DataSets queries those data stores for data. So in this case, you'd return a DataTable or DataSets (System.Data namespace) from a database backend, and then query those using the LINQ syntax

Performing ORDER BY on DISTINCT on Linq to NHibernate(version 3)

You will received errors when doing OrderBy on Distinct result on Linq to NHibernate (NHibernate 3) at the time of this writing:

var cat = session.Query<Product>().Select(x => x.Category).Distinct().OrderBy(s => s);

Convert it to:

var cat = session.Query<Product>().Select(x => x.Category).OrderBy(s => s).Distinct();

Alternatively you can do this, which is quite neat:

var cat = 
        (from c in session.Query<Product>()
        orderby c.Category 
        select c.Category).Distinct();

Note the last two codes produces this(which is performant, heaven thanks):

select distinct category 
 from product 
 order by category asc

Not this:

select distinct category
 from 
 (select category from product
 order by category)

Be aware that if you are using Linq to SQL, the 3rd code construct cannot construct proper query (ORDER BY is omitted on generated query, silent error). Documented here: http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx

He advises to move the orderby out of query to .Distinct() extension method.

var cat = (from c in session.Query<Product>()    
    select c.Category).Distinct().OrderBy(s => s);

Which leads to attaching two extension methods on the query just to make Linq to SQL emit the correct SQL. Which IMHO, renders the whole point of making Linq as query-like as possible lame.


I prefer the Linq to NHibernate approach than Linq to SQL. Not because NHibernate is database-agnostic(but it certainly adds appeal), but for the reason that it correctly informs the programmer that it cannot do something by not performing silent errors; it fail fast.

Here's the error emitted when performing OrderBy on Distinct expression:

Unhandled Exception: System.NotSupportedException: Operation is not supported.