In my previous post, we examined some of the challenges associated with developing data models for master data entities managed within an MDM environment, and I implied that achieving the holy grail of MDM, namely the (elusive, if not purely mythical) “golden record” would first require a practice for business-oriented data conceptual and logical data modeling. We tend to have a “dimensional attitude” about master data, focusing on “customer master” or “supplier master” or “product master” as i...

Since version 4.0, the Entity Framework has had the ability to query un-mapped data and project it onto POCOs using ad-hoc SQL. Here, for example, is how we check the current SQL Server version: internal class SqlVersionInfo { public string Edition { get; set; } public string ProductLevel { get; set; } public string ProductVersion { get; set; } } private static SqlVersionInfo GetSqlServerVersion(ObjectContext conte...

We got a bug report from a customer the other day; a certain query in one of our web services was giving the following error: A column has been specified more than once in the order by list. Columns in the order by list must be unique. Seems clear enough, except that There was no duplication in the ORDER BY. Our DBA discovered that the "problem" reference was [Project2].[C5] ASC, which is an alias, not a real DB column name. It certainly appeared only once, but removing it made the error ...

In an application which uses the Entity Framework, you may see the following error at runtime: MetadataException: Unable to load the specified metadata resource The cause of this error is a missing or malformed Entity Framework connection string. In particular, the cause of this error is the metadata parameter of the connection string. Let's examine this more closely. Which Config File? Before you begin troubleshooting, make sure you are looking at the correct file. Connection strings are...

If you have a list, array, or query in a C#/LINQ application and need to check and see if the list is empty, the correct way to do this is to use the Any() extension method: if (q.Any()) { Similarly, you can check to see if any elements in the list meet a certain condition: if (q.Any(i => i.IsSpecial)) { If the query provider is something like LINQ to Entities, this will be translated into fairly efficient SQL using EXISTS. For some reason, I see a lot of people write this co...

One very popular option for implementing user security in ASP.NET is to use Forms Authentication with the SQL Server membership provider. This provider creates several database tables to store user-related information, as well as a number stored procedures. From time to time, a developer will attempt to add the ASP.NET Membership/Forms Authentication tables to their Entity Framework model (or LINQ to SQL, NHibernate, etc.) model. Before doing this, they will often have created referential con...

As you're probably aware, an Entity Framework model is stored in a single XML file, with the extension EDMX. Developers occasionally ask if this means that two people cannot work on the entity model concurrently. My answer to this is, "It depends." But I can give you some tips to make it easier. Obviously, if you use a source control tool which locks files on check out, then working concurrently on just about anything will be impossible. So I'm going to presume that your source control tool s...

In this post I will demonstrate that use of the join keyword in LINQ to SQL and LINQ to Entities is nearly always wrong. LINQ queries which you write with the join keyword are harder to read and write than queries you write using associations, and they require knowledge of database metadata which is not required otherwise. This introduces the potential for errors and makes maintenance harder. Many people ask how to do a "left join" in LINQ to SQL, and unfortunately, the answer they nearly alw...

In this post, I will demonstrate how to map entity models to views in an ASP.NET MVC application without worrying about implementation details like eager loading, lazy loading, or having to manually optimize SQL for the task at hand. I will argue that expressing the relationship between an entity model in the presentation model in a LINQ projection is far simpler than other methods of doing this mapping. Imagine that you've been asked to write a new web application to track employees for a cu...

Jim McKeeth interviewed me for Episode 34 of The Podcast At Delphi.org....