Using the Web.Config connection string with LINQ to SQL

When updating a project to use LINQ to SQL, I found an issue with deploying to multiple environments.  Each environment (development, staging, live) had its’ own database associated with this.  Since I had the .dbml in another assembly, it was only reading from the app.config in the assembly it resided in.  I was storing the database connection string in the web.config of the project so I thought it would be nice to just use that instead of the app.config.

The first thing I needed to do was to keep the .dbml file from reading from the app.config.  After opening up the .dbml file, I opened the properties window for the file.  In the properties window, there is a setting for “Connection”.  In the “Connection” dropdown I selected the “(None)” selection.  That keeps the .dbml file from accessing the app.config for the database connection string.



The "Connection" setting in the .dbml Properties

Now I needed to get my MainDataContext to use the Web.Config connection string.  For this I created a partial class for my MainDataContext and created a constructor that passed the connection string from the Web.Config.

public partial class MainDataContext
{
    public MainDataContext()
        : base(System.Configuration.ConfigurationManager.ConnectionStrings["Database.connection.string.from.web.config"].ToString(), mappingSource)
    {
        OnCreated();
    }
}

Now when I deploy to different environments the .dbml file is accessing the correct database instead of the same one from the app.config.

Advertisements

Linq Expressions on an Interface

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

One thing I found myself doing quite a bit an often causing logic errors was comparing
dates in Linq queries. For example, if I want to get games that occur between a date range, I might be
doing something like this :

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
				where comp.Date >= startDate && comp.Date <= endDate
				select comp);
	}

I then want to get a list of tournaments that occur between a date range, and I might
essentially rewrite this code using a couple different variables. Rather, I can implement
a common interface on these classes and have an expression applied to all classes that
implement this interface.

	public interface IDate
	{
		DateTime Date { get; set; }
	}
	
	public partial class Competition : DataBase, IDate
	{
		...
	}
	
	public static class Expressions
	{
        public static Expression<Func<T, bool>> GetBetweenDates<T>(DateTime start, DateTime end)
            where T : IDate
        {
            return (t => t.Date >= start && t.Date <= end);
        }	
	}	

Then, rather than rewriting this for each class with a date, I can just apply this expression
to my queries.

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
			select comp).Where(
                             Expressions.GetBetweenDates<Competition>(startDate, endDate));
	}

Avoid static variables in ASP.NET

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Occasionally, I like to write static methods on classes. They are useful whenever the method loosely relates to to the class – but it doesn’t involve a specific instance of the class.

A good example of a static method in the .NET library is the int.Parse(string val) method. This method basically reads a string and tries to return an integer representation. The method logically fits with the int Class (I suppose a string could have a ToInt() instance method…but that would be overwhelming as you added ToFloat(), ToDecimal(), To…()) – but when you run it, there’s no reason to have an instance of int already. You run it to create a new instance of int.

Commonly, I follow similar logic in the business layer of my webapps. I write a static Add(param1,….,paramx) method that returns an instance of the class after it is persisted to the database. I could accomplish this other ways, but I think the resulting application code reads better like:

      User.Add(username,password);

Than:

      new User(username,password).Add();

or, worse:

      DataContext.Users.InsertOnSubmit(new User(username,password));
      DataContext.SubmitChanges();

The issue with static methods in your business logic is that you often need a common object to talk to the database through: a SqlConnection, a TableAdapter, A LINQ DataContext, etc. I could certainly create those objects locally inside each static method, but that’s time consuming and hard to maintain. I want instead to define a common property (in the business class or a parent class of it) that lazy-initializes and returns an instance of the object when I need it. The problem is that the property must also be static for a static method to have access to it.

The easiest way to accomplish this is something like:

      private static ModelDataContext dataContext=null;
      protected static ModelDataContext DataContext
      {
            get
            {
                 if(dataContext==null)
                     dataContext = new ModelDataContext();
                 return dataContext;
             }
       }

The tricky thing is that this will probably work in development and testing, until you get some load on your website. Then, you’ll start seeing all kinds of weird issues that may not even point to this code as the problem.

Why is this an issue? It’s all about how static variables are scoped inside a ASP.NET application. Most web programmers think of each page in their application as its own program. You have to manually share data between pages when you need to. So, they incorrectly assume that static variables are somehow tied to a web request or page in their application. This is totally wrong.

Really, your whole website is a single application, which spawns threads to deal with requests, and requests are dealt with by the code on the appropriate page. Think of a page in your webapp as a method inside of one big application, and not an application of its own – a method which is called by the url your visitor requests.

Why does this matter? Because static variables are not tied to any specific instance of any specific class, they must be created in the entire application’s scope. Effectively, ASP.NET static variables are the same as the global variables that all your programming teachers warned you about.

That means that, for the property above, every single request/page/user of your website will reuse the first created instance of DataContext created. That’s bad for several reasons. LINQ DataContexts cache some of the data and changes you make – you can quickly eat up memory if each instance isn’t disposed fairly quickly. TableAdapters hold open SQLConnections for reuse – so if you use enough classes of TableAdapters, you can have enough different static vars to tie up all of your db connections. Because requests can happen simultaneously, you can also end up with lots of locking/competing accesses to the variable. Etc.

What should you do about it? In my case, I take advantage of static properties that reference collections that are scoped to some appropriately short-lived object for my storage. For instance, System.Web.HttpContext.Current.Items:

      protected static ModelDataContext DataContext
      {
            get
            {
                 if(System.Web.HttpContext.Current.Items["ModelDataContext"]==null)
                     System.Web.HttpContext.Current.Items["ModelDataContext"] = new ModelDataContext();
                 return (ModelDataContext)System.Web.HttpContext.Current.Items["ModelDataContext"];
             }
       }

In this case, each instance of DataContext will automatically be disposed for each hit on the site – and DataContexts will never be shared between two users accessing the site simultaneously. You could also use collections like ViewState, Session, Cache, etc (and I’ve tried several of these). For my purposes, the HttpContext.Items collection scopes my objects for exactly where I want them to be accessible and exactly how long I want them to be alive.

Using Custom Properties Inside LINQ to SQL Queries

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

One thing that initially caused me some trouble with Linq to SQL was that properties
and functions defined on the object cannot be translated to SQL. For example, this code throws an
“The member ‘Table1.DisplayName’ has no supported translation to SQL.” exception when executed.

public string DisplayName
   {
      get
      {
         return this.NameFirst + " " + this.NameLast;
      }
}

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.DisplayName);
}

One simple solution is to just define your sort option each time it needs used.

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.NameFirst + " " + t.NameLast);
}

This option is pretty tedious to write and maintain, especially if you’re going to be using the function often.

Another option is to define a System.Linq.Expression, which is a lambda expression that can be converted to SQL.

static Expression<Func<Table1, string>> DisplayNameExpr = t => t.NameFirst + " " + t.NameLast;

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(Table1.DisplayNameExpr);
}

Note that if you do choose this way, the “DisplayName” is essentially defined in two different places. To solve this problem, use the expression to define the property.

{
   get
   {
      var nameFunc = DisplayNameExpr.Compile(); // compile the expression into a Function
      return nameFunc(this); // call the function using the current object
   }
}

Note this method can also be used to define functions that accept arguments,  so the following code also works as expected.

static Expression<Func<Table1, bool>> HasReq(int numReq)
{
   return (t => (t.IntItem + t.AnotherInt) > numReq);
}

public static IQueryable<Table1> GetWithReq(int req)
{
   return (from t in DataContext.Table1s
             select t).Where(Table1.HasReq(req));
}

Linq for search queries with multiple optional parameters

Several months ago, I began using Linq on one of our major projects.  Initially, I didn’t really understand how beneficial it would be, but perhaps that was a good lesson to give new things a fair shake before discounting them.  After all, there was probably a good reason for creating it, or they wouldn’t have gone to the trouble.  Anyway, I have been very impressed, to say the least.  The speed with which I can create new queries to get the data that I need is remarkable, indeed.

Reflecting now on what we used to do, I can’t help but quote the old Billy Joel song, “The good old days weren’t always good, and tomorrow ain’t as bad as it seems.”

Invariably, there comes a point where we needed to add a column to a table and then regenerate the Dataset queries.  Most of the time, this was pretty easy, but every once in a while, one of the regenerated queries decided to reorder its parameters, unbeknownst to us.  On a good day, this became immediately obvious because the parameter types were different.  On a bad day, however, which became a bad day even it was a good day, the parameters types would be the same and no compile error would alert us to what had happened.  Those were sometimes difficult to even notice and then even more difficult to find.

Then you had the common search page, where the user may or may not specifiy a myriad of various search items.  This gave rise to either one heck of a Sql query with multiple tests for -1 or a just a lot of individual queries.  Neither option was very pleasant.

SELECT *
FROM ThisTable
WHERE  ((ThisColumn = @ThisColumn) || (ThisColumn = -1))
AND ((ThatColumn = @ThatColumn) || (ThatColumn = -1))
AND (IAmGoingInsane = true)

Of course, this gave rise to major efficiency issues as well in the speed of the search.

Enter Linq. (applause)

This seems obvious to me now, but it didn’t occur to me at first.  One of the nice things about Linq is that it builds the query in the code and doesn’t execute it until you, so you can basically add only those parameters that the user has selected, like this:

var results = (from theStuff in DataContext.Stuffs
where theStuff.NameLast == nameLast);
if (nameFirst != "")
results = results.Where(r => r.NameFirst == nameFirst);
if (city!= "")
        results = results.Where(r => r.City == city);
return results;

Since the code only adds the parameters you need, the Sql query can still be optimized, and you don’t need a bunch of
different queries–just one, with all the parameters in it.

Pretty nice. I’m very happy, indeed, that the good old days of DataSets are gone. Thanks, Linq!

LINQ to SQL DataContext Initializer

In LINQ to SQL, you design a model by dropping tables from your DB onto a designer surface.  The model creates a DataContext, which allows you to reference your tables in the model as (something like) a collection you can query with LINQ.  You can also call SubmitChanges() on the DataContext to persist any local changes you made back to the database.

One way to do this in the webapp would be something like:

var datacontext = new ModelDataContext();
var user = (from u in datacontext.Users where u.UserID==5 select u).First();
user.Email = "newemail@email.com";
datacontext.SubmitChanges();

This isn’t ideal, because you are peppering your webapp with data/business logic – just because you need to keep an instance of a datacontext. You could pull out the data logic, but you’d still need to new up an instance of the data context and pass it to each business logic function – that would be a mess. I’d prefer the webapp to not know/care about persistence or a DataContext at all. It would be great if the Business logic classes could handle that.

The problem is, lots of the business logic uses a static method ( User.Add(“username”,”password”) – you shouldn’t need an instance of User to run that, it works like a Factory pattern and returns an instance). A local DataContext instance would have to be static to be accessible to those functions – and ASP.NET treats local statics something like global/application variables. One instance would be shared across every request and every user on the site. DataContext instances are supposed to be available for limited amount of time – reusing one in this way would create all kinds of weird issues under any kind of load.

So, what I needed was somewhere to store the instance – shared across multiple business logic calls but scoped to exactly one request. After some trial and error, I ended up storing it in different locations for ASPX pages, for ASMX webservices (standard, and WSE 3.0), and for non-ASP.NET uses (particularly LinqPad). First, I used a interface to implement for each type and cast to get the DataContext:

public interface IDataContextHelper
{
	ModelDataContext DataContext { get; }
}

Next, I implemented that handler for the special page class that all ASPX pages use:

public class FoliotekPage : System.Web.UI.Page, Foliotek.DataAccess.IDataContextHelper
{
.
.
.

	private Foliotek.DataAccess.ModelDataContext dataContext = null; //local and non-static, so scoped with the page handler
	public Foliotek.DataAccess.ModelDataContext DataContext
	{
		get
		{
			if (dataContext == null)
			{
				dataContext = new Foliotek.DataAccess.ModelDataContext();

			}
			return dataContext;
		}
	}
.
.
.
}

For various reasons, I didn’t have access to the CurrentHandler to do this for webservices and ashx files. Instead, I used the Items collection on the ASHX handler class, and the .Current[] collection on the SoapContext classes:

Standard webservice:

public class AjaxSupport : System.Web.Services.WebService
{

	public AjaxSupport()
	{
		this.Context.Items["ModelDataContext"] = new Foliotek.DataAccess.ModelDataContext();
	}
.
.
.
}

WSE 3.0 webservice (security object)

SoapContext.Current["ModelDataContext"] = new dac.ModelDataContext();

Finally, I tied it all together by writing a property in the super class for all of my business logic classes:

static ModelDataContext datacontext;
internal static ModelDataContext DataContext
{
	get
	{

		if (System.Web.HttpContext.Current == null) // for linqpad/etc
		{
			if (datacontext == null)
				datacontext = new ModelDataContext();
			return datacontext;
		}
		else if (System.Web.HttpContext.Current.CurrentHandler is IDataContextHelper) // for web page
		{
			return ((IDataContextHelper)System.Web.HttpContext.Current.CurrentHandler).DataContext;
		}
		else if (System.Web.HttpContext.Current.Items != null &amp;&amp; System.Web.HttpContext.Current.Items["ModelDataContext"] != null) // for simple webservice
		{
			return (ModelDataContext)System.Web.HttpContext.Current.Items["ModelDataContext"];
		}
		else if (Microsoft.Web.Services3.SoapContext.Current != null) // for webservices (in auth)
		{
			return (ModelDataContext)Microsoft.Web.Services3.SoapContext.Current["ModelDataContext"];
		}
		else if (Microsoft.Web.Services3.RequestSoapContext.Current != null) // for webservices (in webservice call)
		{
			return (ModelDataContext)Microsoft.Web.Services3.RequestSoapContext.Current["ModelDataContext"];
		}
		else
		{ // make sure we know about it if something uses the static context, that can be bad under load...
			ErrorLog.Add(-1, -1, -1, "Warning - Using Static Data Context","",
			"", "", 80, System.Web.HttpContext.Current.Request.Url+"", "", "", new 	Foliotek.DataAccess.ErrorLog.QueryValueDataTable(), "",
			"", "", "", "", "");
			if (datacontext == null)
				datacontext = new ModelDataContext();
			return datacontext;
		}
	}
}

I think the methodology is sound. I happened upon the Items collection on HttpContext fairly recently – its possible that is a solution that would cover all of the ASP.NET cases. You might even be able to instantiate it on Application_BeginRequest and clear it on Application_EndRequest and clear out some of the extra code. Here’s the resulting code:

Web App:

var user = Business.User.Get(userid);
user.Email = "newemail@email.com";
user.Update();

Business Class:

public static User Get(int userid)
{
	return (from u in DataContext.Users
	where u.UserID == userid
	select u).First();
}

public void Update()
{
	DataContext.SubmitChanges();
}

So, all that work had 2 main benefits: the web app developer doesn’t have to think about persistence at all, and the business logic developer doesn’t have to deal with managing a DataContext instance. Less than 100 lines of code eliminated several hundred lines of boiler plate code.