Only parameterless constructors and initializers are supported in LINQ to Entities

Want to populate a Dictionary of type long, Tuple using LINQ to Entities? Well you can’t.  But you can use LINQ to Entities to return an object and then use LINQ to Objects to populate your dictionary.

Dictionary<long, Tuple<string, string>> pages = 
                               new Dictionary<long, Tuple<string, string>>();

To populate, you would expect the following to work:

pages = (from n in dbContext.Navigations
         select new
         {
             Key = n.PageId,
             Value = new Tuple<string, string>
             (n.Page,
             n.Link)
         }).ToDictionary(n => n.Key, n => n.value);

But, of course I’m using LINQ to Entities so at run-time the compiler says “Only parameterless constructors and initializers are supported in LINQ to Entities”! In LINQ to SQL you are allowed to do this, but not LINQ to Entities, so is this a bug? Nope, Microsoft has done this by design. From what I’ve read it basically boils down to this: LINQ allows you to combine server (SQL) code and client (.NET) code in your query and during execution LINQ will try to figure out what is what. LINQ to Entities forces separation of concerns here and so you cannot mix .NET and SQL code together.

So, if we can’t combine the two together (like in our example above), how do we populate our dictionary? Well, there are two ways. The first step is the same no matter which way you go, get the data from the server. From there you can iterate through the data through a foreach loop and populate the dictionary that way, or you can use LINQ to populate the dictionary. Everyone knows how to use a foreach loop, so I’ll just show you the LINQ method.

First, retrieve the data from the server:

var fromServer = (from n in dbContext.Navigations
                  select new
                  {
                      Id = n.PageId,
                      Page = n.Page,
                      Link = n.Link
                  }).ToList();

Then, use LINQ to populate the dictionary:

pages = (from n in fromServer
         select new
         {
             Key = n.Id,
             Value = new Tuple<string, string>
             (n.Page,
              n.Link)
         }).ToDictionary(n => n.Key, n => n.Value);

You might note that this LINQ statement looks very familiar to the initial LINQ query, but remember, the first didn’t work because LINQ to Entities does not know how to instantiate (in this case) a new Tuple on SQL Server. The LINQ statement above works because LINQ to Objects runs on the client and therefore knows about Tuples.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s