Will Beattie's Blog

  If you’re on the .NET Platform then MS SQL Server is usually the de-facto choice for the RDBMS. However if you’re at all cost conscience then you will realize that scaling and replication is going to cost you a fair chunk of change in licensing fees. For that reason open source RDBMSs and in particular MySQL offer a much cheaper alternative. In this post I’d like to demonstrate how you can use Entity Framework 4 with MySQL. MySQL Connector Net 6.3.6 The first thing you’ll need to do is download and install the latest version of the MySQL Connector for .NET from http://dev.mysql.com/downloads/connector/net/ Make sure that Visual Studio is closed when you install. Pascal Case Table Names Because we are going to generate our Entity Framework Model of an existing database we want to make sure that the entity names use pascal casing. By default MySQL on Windows forces lowercase table names. You can change this behaviour by adding lower_case_table_names=2 to your my.ini file...

  Firstly just to clarify I am going to be talking about the OOP Specification Pattern not the data pattern commonly found in the SID (Shared Information & Data) model. Much has been said about the specification pattern so I’m not going to go into that, if you want an overview check out these posts: http://www.lostechies.com/blogs/chrismissal/archive/2009/09/10/using-the-specification-pattern-for-querying.aspx http://devlicio.us/blogs/jeff_perrin/archive/2006/12/13/the-specification-pattern.aspx In this post I’m going to demonstrate how you can make use of the specification pattern to query Entity Framework and create reusable, testable query objects and eliminate inline LINQ queries. The Smell When I first got started with Entity Framework way back in 2008 when EF was still in it’s infancy we had lot’s of inline LINQ all over the code base and specific methods on our repositories for querying requirements (which any OOP purist will tell you is bad). We had ...

  UPDATED ON: 16/09/2010 If you’re using Entity Framework chances are you’ve come up against performance issues already, especially when instantiating your Object Context. One very reliable way to increase performance is to pre-generate the Views. Depending on the size of your model and in my experience it can shave as much as 40% off the instantiation time. There is a good overview on MSDN , however it only covers .NET 3.5. Step 1 Go to your Model properties and select “ Copy to Output Directory ” for the Metadata Artifact Processing option. The result of this is you will end up with the .ssdl, .csdl and .msl files in your output directory which in this case is bin/Debug. Step 2 Next you need to setup the Pre-build event to use the EDMGen.exe tool. NB: If you use the Command on the MSDN site you will come across this error. “The required parameter ‘mode’ is missing” The correct command to use is: "%windir%\Microsoft.NET\Framework\ v4.0....

If you’re using Sql Server 2008 with Entity Framework and have a DateTime field you have no doubt come across this error when Inserting or Updating an Entity System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The problem here lies in the fact that the .NET DateTime.MinValue equals 0001-1-1 but the Sql Server DateTime only covers 1753-1-1 through 9999-12-31 The very simple solution is to use DateTime2 in Sql Server 2008 which covers 0001-1-1 through 9999-12-31. DateTime2 has also has some other benefits .

Recently I had an Advanced Search requirement which had to enable Users to specify a combination of fields to search for and the operator e.g. AND, OR and the type of comparison to do e.g. LIKE, EQUALS. The DataLayer is built upon the Entity Framework and I'm using LINQ to Entities as the query mechanism. So my overriding goal was to keep this Strongly Typed but maintainable as well. Firstly I created a generic SearchInfo object of which there would be one for each field that needed to be included in the search. public class SearchInfo<T> { public Expression<Func<T, object>> PropertySelector { get; set; } public QueryOperator Operator { get; set; } public StringComparer Comparer { get; set; } public string Value { get; set; } } public enum QueryOperator { And = 0, Or = 1 } public enum StringComparer { Equals = 0, StartsWith = 1, EndsWith = 2, Contains = 3 } Now if we just had to deal with the AND operator we could achieve this by con...