Derik Whittaker

Gotta say, I am kinda flustered right now.  Today I was trying to perform what I thought to be a pretty straight forward Linq2Sql query, but turns out it was not.

Overview

I have a query that joins a few tables together to get some data, but in place of returning explicit instance of a data entity, I wanted to do a select new XXXX.  I wanted to do this because I had extended the main entity to have a extra property that is needed based on my domain.  And because I did NOT want to create a who new entity, I thought this was the path with the least resistance.  WRONG.

Here is what I WANTED to do.

var items = ( from ctl in DBContextInstance().CampaignTrackingLinks
         join cts in DBContextInstance().CampaignTrackingSummaries on ctl.CampaignTrackingSummaryID equals cts.ID
         join ctcts in DBContextInstance().CampaignTrackingClickThroughStats on cts.ID equals ctcts.CampaignTrackingSummaryID
         where cts.SendID == sendID
         where ctl.Url == ctcts.URL
         select new CampaignTrackingLink
         {
             TotalClicks = ctl.TotalClicks,
             UniqueClicks = ctl.UniqueClicks,
             Url = ctl.Url,
             Alias = ctl.Alias
             PercentOfClicks = ctcts.PercentOfClicks
         }
       ).ToList();

However, every time I ran this code I received  the following exception 'Explicit construction of entity type 'XXXXX.CampaignTrackingLink' in query is not allowed.'.  What was throwing me at first was I KNEW that my syntax was correct, but it still did not work.

After a bit of googling I found (here) something that I think may explain why this is happening. 

The solution to get this to work was not to create a new instance of my extended Linq entity, but to create a whole new entity that inherits off of my Linq entity.  For some reason this worked

    public class CampaignTrackingLinkBreakdown : CampaignTrackingLink
    {
        public double PercentOfClicks { get; set; }
    }

It really sucks that I needed to create a new class that inherited off my Linq entity, but you do what you have to get it working.

var items = ( from ctl in DBContextInstance().CampaignTrackingLinks
         join cts in DBContextInstance().CampaignTrackingSummaries on ctl.CampaignTrackingSummaryID equals cts.ID
         join ctcts in DBContextInstance().CampaignTrackingClickThroughStats on cts.ID equals ctcts.CampaignTrackingSummaryID
         where cts.SendID == sendID
         where ctl.Url == ctcts.URL
         select new CampaignTrackingLinkBreakdown
         {
             TotalClicks = ctl.TotalClicks,
             UniqueClicks = ctl.UniqueClicks,
             Url = ctl.Url,
             Alias = ctl.Alias,
             PercentOfClicks = ctcts.PercentOfClicks
         }
       ).ToList();

So as you can see, I get the same end result, but have to jump through a few extra hoops.

Based on the feedback from the forum link above, this issue is due to the fact that Linq attempts to keep track of all entities for change state reasons and allowing for explicit construction via a query could cause a caching issue.  In my situation, I DO NOT care about change state as this is a read only entity so there will be no updates/inserts done later.

Oh well, at least now I know the issue and how to work around it.

If anyone knows of a better/cleaner way to solve this issue, please let me know.

Till next time,