Monthly Archives: February 2008

Executing custom queries with LINQ (ExecuteQuery)

In a project I’m working on, and using LINQ, there have come a few times when I have needed to create my own SQL and send a query to the database. This has typically happened when I’m not doing queries that aren’t very straight forward, but I still want to accomplish the task with only one round trip to the database.

The DataContext object has a generic method ExecuteQuery for just such occasions. You pass it a query string with string.Format style replacement parameters ({0}, {1}, etc) and the arguments to fill into those values. It runs your query and returns an IEnumerable. This is pretty handy, and I have used it before returning a list of decimals and things like that. But it really gets cool when you want to use custom types.

To this point, I have only used the LINQ DataContext to retrieve simple simples, such as a decimal, or a LINQ type defined in my .dbml file. In the case of the ExcuteQuery method you can put your own custom class as the TResult generic. The method then returns an IEnumerable. The mapping is determined by the following rules: (source: http://msdn2.microsoft.com/en-us/library/bb361109.aspx )

  • If a field or property is mapped to a particular column name, that column name is expected in the resultset.
  • If a field or property is not mapped, a column with the same name as the field or property is expected in the resultset.
  • The comparison is performed by first looking for a case-sensitive match. If such a match is not found, a subsequent search occurs for a case-insensitive match.
  • The query must return all the tracked fields and properties of the object (apart
    from those subject to deferred loading) when all the following are true:

    • If <T> is an entity explicitly tracked by the DataContext.
    • ObjectTrackingEnabled is true.
    • The entity has a primary key.

    Otherwise an exception is thrown.

  • In all other cases, the query can retrieve just a subset of the tracked fields
    and properties for the object.

This means that you if set up your query result column names correctly, you don’t have to do anything else to map the query to your object. This is a huge improvement to what I have trudged through in the past with other technologies.

Deploying ASP.Net 3.5 apps to a new IIS Server

For a project at work, I have been developing an ASP.Net app using .Net version 3.5. I have been using the built in Cassini web server for development, but finally the moment of truth came to put it on IIS on our development server and I had a few hickups. I am using Windows Server 2003.

1. Don’t look for version 3.0 or 3.5 in your drop down list.
From what I can gather on the web (a Microsoft MSDN Online Support Lead at http://forums.iis.net/p/1133618/1860351.aspx, for instance) “As for ASP.NET 3.5, it is still based on the ASP.NET 2.0 core fundamental. Therefore, there is no new application type(for ASP.NET 3.5) in IIS, still use the 2.0 one.”

This seemed pretty strange and a little conerning to me, but so far things are working. If I find trouble because of this, then I’ll raise concerns.

2. Don’t forget to allow the ASP.Net web service extension.
This one is a little bit less obvious. If you just publish your web application to the IIS server, everthing looks right. The settings on your virtual directory / application show that it is using ASP.Net and all the properites seem correct. Yet when you make a request to any aspx page you get a simple 404 “Page cannot be found” error. That’s it, no clues as to what’s breaking.

Before an ASP.Net application will run for the first time, you have to go into the IIS manager utility, click “Web Service Extensions” find the ASP.Net process in the list and select “Allow.” This did the trick for me, and now everything is up and running.