Derik Whittaker : Linq

Entity Frame Performance Gotcha

Derik Whittaker — Tue, 12 Feb 2013 19:44:19 GMT

I was doing some profiling of our Entity Framework stuff via EFProfiler the other day and noticed something really odd. When I was watching the profiler I noticed that I had a query returning back over 1300 results and that was NOT right, I had expected only 1 result. I looked the resulting sql and immediately I ‘thought’ I knew the issue. I thought the issue was that I had not setup my where clause correctly in my EF… Turns out I was both right and wrong at the same time.

Below is a sample of my EF code

** note that I replaced the dynamic variables w/ static for testing inside of LinqPad **

When I run these statements above and look at the resulting sql in LinqPad I would expect to see 2 queries. Sure enough I did have 2 of them. What shocked me was that the 2nd query ONLY did a where clause filter on ClientID (see below)

This had a resulting output of below, which is the expected result

But if you run the sql generated above (2nd statement) you actually get 1327 results. This tells me that indeed the ENTIRE result for a client was being pull out of the DB, pulled over the wire and filtered down on the client…This is not good.

Now I tried a few different things to see if I could get different results such as doing a .FirstOrDefault rather than a .Where… No dice

Finally one of my coworkers found this Stack Overflow link which talks directly to this issue.

Turns out this behavior is by design and I had never noticed it before. I (and 3 of my coworkers mind you) had just assumed that when EF did a lazy load off of a previously fetched IQueryable it would include any filter statements and pass those to the SERVER. But because the .Images is NOT a IQuerable but rather an EntitySet<T> it did not work as expected.

To solve this issue I simply just changed my statement to go directly at my EF context for the ONE row I wanted w/ both keys (client and type). This solved my issue as well as give me a pretty significant performance enhancement.

Long story short is that if you are going to lazy gather data in EF make sure you are doing your filtering on the server rather than the client and make sure you understand that an EntitySet does NOT do this only an IQuerable does this.

Till next time,

Linq rocks, except when it doesn’t. Can anyone spot the bug in this code?

Derik Whittaker — Fri, 21 Jan 2011 23:10:59 GMT

I am in the middle of building out some wcf endpoints to support the Dimecasts.net WP7 application I am going to publish. The code below is some of my main logic which helps grab the episodes. Because the data for Dimecasts is not very volatile I am going to do some basic (aka primitive) caching.

What I am looking to do is save the amount of data which needs to be gathered from the db. The intent of the code below is to get all data from cache which is greater then the last known episode which is cached on the phone. If data is found in the cache we will adjust the ‘last known episode’ in order to again reduce the db hit. However, there is a slight bug.

Who can spot the bug?

        public IList<EpisodeModel> FetchEpisodes( Int32 lastKnownEpisodeId )
        {
            var episodes = new List();
            var cachedEpisodes = Cache.FetchEpisodes()
                .Where( x => x.EpisodeNumber > lastKnownEpisodeId )
                .OrderBy( x => x.Id );

            if ( cachedEpisodes.Count() != 0 )
            {
                // reset the last known id in order to reset the reset
                lastKnownEpisodeId = cachedEpisodes.LastOrDefault().Id;

                episodes.AddRange( cachedEpisodes );
            }

            // more code which does not matter here.....

            return episodes;
        }

If you know the issue post it in the comments. If it is not ‘solved’ I will post an update with the solution and the reason.

Till next time,

Little nugget of simplification when loading an XDocument

Derik Whittaker — Wed, 02 Jul 2008 14:53:27 GMT

Today I needed to load in an XML file into memory for later use. At first I was going to use the XmlDocument library (old habits die hard), but later switched over to the XDocument library. Why, not because one is better (ok, so I think that XDocument is better, but not the point here), but because it caused me less friction (which I guess does make it better).

Consider this need. I have a list of XML files on disk and I would like to convert them to a list of XML documents.

Below is my original code using the XmlDocument library:

...

IList returnDocument = new List< XDocument >();
foreach ( string file in files )
{
        XmlDocument xmlDocument = new XmlDocument();
        xmlDocument.Load( file );
	returnDocument.Add( xmlDocument );
}

...

Below is my new code using the XDocument library:

....
IList returnDocument = new List< XDocument >();

foreach ( string file in files )
{
	returnDocument.Add( XDocument.Load( file ) );
}
....

Now there is no real difference in what each example does, but there is a difference in how I have to accomplish my need.

Using the XmlDocument library I need to create an instance, load the instance and finally put it into my return array.

Using the XDocument library, I can simply use the static Load to accomplish this in a single line.

+1 for less code.

Till next time,

[----- Remember to check out DimeCasts.Net -----]

Using Linq's DataContext to (re)create your database for testing

Derik Whittaker — Wed, 21 May 2008 16:24:12 GMT

If you happen to be using Linq2Sql and are also someone who tests, I have something for you. You can use Linq's DataContext to create your database and then populate it with test data prior to each test run. Note that you can do this same thing when using other OR/M tools such as NHIbernate, so this is nothing new or revolutionary.

Before I get into the 'How To' on doing this, I would like to go over the 'Why' I should do this.

When building out your test suit on any data driven application you are going to need to test data access at some point. There are really 3 solutions to this problem.

Simply query for known, existing data
Although this works, this leads to very weak and brittle tests for obvious reasons.
Create/use/delete the data for each of your tests
This is a better choice, but leads to very heavy tests and can also lead to bad or orphaned data. Which can be a major pain point if this is done in your development environment.
Rebuild the db and test data for each test run.
To me, this is the best choice as you get a clean slate and any orphaned data will be destroyed after each run. No harm, no foul

Ok, on to the How to of this post.

The first thing you need to do is setup your tests to have your test fixture have FixtureSetup/FixtureTearDown methods to perform building/destroying of the database.

private TestsInit _testsInit;

[TestFixtureSetUp]
public void FixtureSetup()
{
    _testsInit = ObjectFactory.GetInstance< TestsInit >();
    _testsInit.InitDatabase();
}

[TestFixtureTearDown]
public void FixtureTeardown()
{
    _testsInit.DestroyDatabase();
}

Once you have your Setup/TearDown implemented you need to create the logic to build the database and insert test data.

public void InitDatabase()
{
    var dbContext = new DBContextDataContext( ConfigurationReader.ConnectionString_ForTests );
 
    CreateDatabase( dbContext );
    CreateTypeData( dbContext );
    CreateTestEpisode( dbContext );
}

public void DestroyDatabase()
{
    var dbContext = new DBContextDataContext( ConfigurationReader.ConnectionString_ForTests );

    DeleteDatabase( dbContext );
}

private void CreateDatabase( DataContext dbContext )
{
    DeleteDatabase( dbContext );

    dbContext.CreateDatabase();            
}

public void CreateTypeData( DBContextDataContext dbContext )
{
    dbContext.ExecuteCommand( "INSERT INTO [LevelType]( Name ) VALUES ( 'Type 1' )" );
    .....
    dbContext.ExecuteCommand( "INSERT INTO [TagTypes]( Name ) VALUES ( 'Type 4' )" );
}

private void CreateTestEpisode( DBContextDataContext dbContext )
{
    Episode episode = new Episode {LevelTypeID = 1, Name = "Test 1", Description = "Desc 1", EpisodeNumber = 1, EpisodeDate = DateTime.Now};
    ....

    episode.EpisodeDownloadInformations = new EntitySet< EpisodeDownloadInformation >
                                              {
                                                  new EpisodeDownloadInformation{ FileName = "Foo.wmv", Size = 9.25m, Time = "10:00" }
                                              };

    dbContext.Episodes.InsertOnSubmit( episode );
    dbContext.SubmitChanges();
}


private void DeleteDatabase( DataContext dbContext )
{
    if ( dbContext.DatabaseExists() )
    {
        dbContext.DeleteDatabase();
    }
}

One thing to be careful of here is that you are using an alternate connection string, not the one for your dev/stage/production environment or you will NOT be a happy developer. Also notice that I both using the ExecuteCmmand statement as well as the object model to insert the test data. This is done simply to show the concept. You could also simple script up all the inserts and put them into an external file. The way you do it is up to you.

So there you go, you now know how you can use the DataContext to build/destroy your db structure for each test run.

Till next time,

XLinq'n in the new world - Speed comparison with Xml/XPath

Derik Whittaker — Wed, 14 May 2008 15:02:22 GMT

Originally this series was meant to be titled 'Any thing you can do I can do better', but to be honest after writing the Xml/XPath examples I realized the XLinq is really no better per say than Xml/XPath. However, what XLinq does bring to the table that Xml/XPath does not:

Does not need to use the XPath query syntax
Reads like English (mostly) when creating queries
Lower barrier to entry for someone new to Xml (my opinion)

For Part 1 of this series check here
For Part 2 of this series check here
For Part 3 of this series check here

In Part 4 of this little mini-series we will examine the speed differences between XLinq and Xml/XPath.

Before I get started I will show you a sample of the XML document I am using. The sample document I am using today is 5.25mb in size.

<?xml version='1.0'?>
<root>
<system>
    <list>
      <subscribers>
        <subscriber Type="Random">
          <id>63425813</id>
          <Email__Address>FakeAddress@comcast.net</Email__Address>
          <EmailType>HTML</EmailType>
          <Status>InActive</Status>
          <First__Name>RAY</First__Name>
          <Last__Name>Bob</Last__Name>
          <PIN__Code>NaN</PIN__Code>
          <PIPIN>33232</PIPIN>
          <UID>418444</UID>
          <ProgNum>0</ProgNum>
          <Title />
          <Username />
          <Password />
        </subscriber>
      </subscribers>
    </list>
    <list>
      <subscribers>
          <subscriber>
          <id>1271728821</id>
          <Email__Address>FakeAddress@yahoo.com</Email__Address>
          <EmailType>HTML</EmailType>
          <Status>InActive</Status>
          <First__Name>JOHN</First__Name>
          <Last__Name>Foo</Last__Name>
          <PIN__Code>NaN</PIN__Code>
          <PIPIN>1254512</PIPIN>
          <UID>1033488</UID>
          <ProgNum />
          <Title />
          <Username />
          <Password />
        </subscriber>
      </subscribers>
    </list>
</system>
</root>

Example 1 - Loading a XML document from Resource Stream

There is no real code to show here, but I do have some speed numbers to chat about. I ran my test 2 times. The first time I loaded into a XLinq object first then into an XmlDocument object and for the second run, I swapped the order.

As it turns out the first document to load each time is slower, but you can get a look at the speed difference by looking at the delta between each load time.

Load Time

XmlDocument	515.00	327.72	187.27 (Delta)
XLinq	358.94	546.2135	187.27 (Delta)

By looking at the above, there is no real speed difference, which I expected. (Time above is in milliseconds)

Example 2 - Finding a Unique value in the Xml Document

For this example I decided to try to find a single node inside my larger xml file. In order to try to get good sample I ran each test 5 times and the times below are the average of all those runs

Run time (5 run avg)

XLinq	44.62
Xml/XPath	125.65

I have to say that I was kinda surprised by the results. I figured that the Xml/XPath way would be a little faster, but turns out it is 3 times slower. (Time above is in milliseconds). ** NOTE *** The times above do NOT include the time needed to load the XML document into the correct object model.

From the information in this mini-series i would conclude that if you need to do any type of XML reading/manipulation/creation I would make the jump over to using XLinq as your preferred way of doing so.

Till next time,

XLinq'n in the new world - Structure Modification comparisons with Xml/XPath

Derik Whittaker — Wed, 14 May 2008 12:21:19 GMT

Does not need to use the XPath query syntax
Reads like English (mostly) when creating queries
Lower barrier to entry for someone new to Xml (my opinion)

For Part 1 of this series check here
For Part 2 of this series check here

In Part 3 of this little mini series we will examine some common data creation scenario's and how XLinq's syntax compares to that of standard Xml/XPath access. In general will we review how to modify the structure of an existing Xml document.

Before I get started I will show you a sample of the XML document I am using.

Example 1 - Adding a New Node w/ Element

via Xml/XPath

// notice the @ is missing on the status -- this is how u search a node
XmlDocument xmlDocument = XmlHelper.GetRawSnippetAsXmlDocument();
XmlNodeList xmlNodeList = xmlDocument.SelectNodes( "//list" );

foreach ( XmlElement element in xmlNodeList )
{
    XmlNode newNode = xmlDocument.CreateNode( XmlNodeType.Element, "NewElement", string.Empty );
    XmlAttribute nodeAttribute = xmlDocument.CreateAttribute( "MyAttribute" );
    nodeAttribute.Value = "MyValue";

    newNode.Attributes.Append( nodeAttribute );

    element.AppendChild( newNode );
}

Via XLinq

XDocument xDocument = XmlHelper.GetRawSnippetAsXDocument();

IEnumerable item = from doc in xDocument.Descendants( "list" )
                             select doc;

foreach ( XElement xElement in item )
{
    xElement.Add( new XElement( "NewElement",
                                new XAttribute( "MyAttribute", "MyValue" ),
                                new XElement( "ChildElement", "ChildValue")) );
}

//ChildValue

One of the big differences you will notice when trying to add nodes/elements to an existing Xml doc with XLinq vs. Xml/XPath is the that with XLinq it is more 'fluent' then with Xml/XPath. To add nodes/elements with Xml/XPath you need to create the nodes/elements via the XMLdocument object.

Example 2 - Deleting and Node w/ Elements

via Xml/XPath

// notice the @ is missing on the status -- this is how u search a node
XmlDocument xmlDocument = XmlHelper.GetRawSnippetAsXmlDocument();
XmlNodeList xmlNodeList = xmlDocument.SelectNodes( "//subscribers/subscriber[Status='InActive']" );

foreach ( XmlElement element in xmlNodeList )
{
    element.RemoveAll();
}

Via XLinq

XDocument xDocument = XmlHelper.GetRawSnippetAsXDocument();

IEnumerable< XElement > item = from doc in xDocument.Descendants( "subscriber" )
                               let status = (string) doc.Element( "Status" )
                               where status == "InActive"
                               select doc;

foreach ( XElement xElement in item )
{
    xElement.Remove();
}

Besides the way you navigate to the node/element you would like to remove the syntax to delete/remove the nodes are really no different. But, again with XLinq you do not need to know XPath in order to navigate to the correct location in the document.

Well, this pretty much wraps up my little mini-series on XLinq'n in the new world. I hope that in the past 3 posts you have learned a little about how to use XLinq and how it differs from Xml/XPath.

Till next time,

XLinq'n in the new world - Data manipulation comparisons with Xml/XPath

Derik Whittaker — Tue, 13 May 2008 10:37:51 GMT

Does not need to use the XPath query syntax
Reads like English (mostly) when creating queries
Lower barrier to entry for someone new to Xml (my opinion)

For Part 1 of this series check here

In Part 2 of this little mini series we will examine some common data manipulation scenario's and how XLinq's syntax compares to that of standard Xml/XPath access. In general will we review how do update the content of both elements and attributes in an XML document.

Before I get started I will show you a sample of the XML document I am using.

Example 1 - Updating Element Values

via Xml/XPath

// notice the @ is missing on the status -- this is how u search a node
XmlDocument xmlDocument = XmlHelper.GetRawSnippetAsXmlDocument();
XmlNodeList xmlNodeList = xmlDocument.SelectNodes( "//subscribers/subscriber[Status='InActive']" );

// Attribute value before updates
// 
// 

foreach ( XmlElement element in xmlNodeList )
{
    // this will only set the value.  if the node does NOT exist you will get an exception.
    element.SelectSingleNode( "//Status" ).InnerText = "Active";
}

// Attribute values afterupdate
//

via XLinq

XDocument xDocument = XmlHelper.GetRawSnippetAsXDocument();

var item = from doc in xDocument.Descendants( "subscriber" )
           let status = ( string )doc.Element( "Status" )
           where status == "InActive"
           select doc;

// Attribute value before updates
// InActive

foreach ( XElement xElement in item )
{
    // this will set the value or create it if the element does not exist
    xElement.SetElementValue( "Status", "Active" );

    // Another option is to go after the element directly, but this requires a 
    //  null check first
    if ( xElement.Element( "Status" ) != null )
    {
        xElement.Element( "Status" ).SetValue( "Active" );
    }

}

// Attribute values after update
// Active

As you can see the differences between using XLinq and Xml/XPath for updating elements is not the big. However, again you have to know/understand how to use XPath to find your nodes. There is ONE thing you need to be aware of though. When trying to update an element via XPath the element MUST exist. If it does not you will get a runtime exception. With XLinq there is a way to do this and not have to worry about that. To me that is pretty nice.

Example 2 - Updating Attribute Values

via Xml/XPath

// notice the @ is missing on the status -- this is how u search a node
XmlDocument xmlDocument = XmlHelper.GetRawSnippetAsXmlDocument();
XmlNodeList xmlNodeList = xmlDocument.SelectNodes( "//subscribers/subscriber[Status='InActive']" );

// Attribute value before updates
// 

foreach ( XmlElement element in xmlNodeList )
{
    // this will set the value or create it if the attribute does not exist
    element.SetAttribute( "Type", "SomeValue" );
}

// Attribute values afterupdate
//

via XLinq

XDocument xDocument = XmlHelper.GetRawSnippetAsXDocument();

var item = from doc in xDocument.Descendants( "subscriber" )
           let status = ( string )doc.Element( "Status" )
           where status == "InActive"
           select doc;

// Attribute value before updates
// 

foreach ( XElement xElement in item )
{
    // this will set the value or create it if the attribute does not exist
    xElement.SetAttributeValue( "Type", "MyValue" );

    // Another option is to go after the attribute directly, but this requires a 
    //  null check first
    if ( xElement.Attribute( "Type" ) != null )
    {
        xElement.Attribute( "Type" ).SetValue( "SomeValue" );
    }
    
}

// Attribute values afterupdate
//

Above shows you that updating attributes are almost identical and in this case there is no need to know/understand XPath in order to select the single node you are trying to update.

Well, above I have shown you a few simple examples that compare Xml/Path to XLinq, I hope you find this useful. The next post will compare how to modify the structure of a document using the both Xml/XPath and XLinq.

Till next time,

XLinq'n in the new world - Data access comparisons with Xml/XPath

Derik Whittaker — Mon, 12 May 2008 10:15:22 GMT

Does not need to use the XPath query syntax
Reads like English (mostly) when creating queries
Lower barrier to entry for someone new to Xml (my opinion)

In Part 1 of this little mini series we will examine some common data access scenario's and how XLinq's syntax compares to that of standard Xml/XPath access.

Before I get started I will show you a sample of the XML document I am using.

Example 1 - Simple data access

via Xml/XPath

XmlDocument xmlDocument = XmlHelper.GetRawSnippetAsXmlDocument();

// notice the @ is on the status -- this is how u search a element
Int32 listCount = xmlDocument.SelectNodes( "//list" ).Count;
Int32 subscribersCount = xmlDocument.SelectNodes( "//subscriber[@Type='Random']" ).Count;

via XLinq

XDocument xDocument = XmlHelper.GetRawSnippetAsXDocument();

Int32 listCount = xDocument.Descendants( "list" ).Count();
Int32 subscribersCount = xDocument.Descendants( "subscriber" ).Attributes( "Type" ).Where( e => e.Value == "Random" ).Count();

As you can see both examples are pretty light on the code and are pretty straight forward. This is one case where the new XLinq syntax is really no better/simpler then the Xml/XPath syntax. The issue with the XPath syntax is that

Example 2 - Get a list of nodes that match our search criteria

via Xml/Xpath

// notice the @ is missing on the status -- this is how u search a node
XmlDocument xmlDocument = XmlHelper.GetRawSnippetAsXmlDocument();
XmlNodeList xmlNodeList = xmlDocument.SelectNodes( "//subscribers/subscriber[Status='InActive']" );

foreach ( XmlElement element in xmlNodeList )
{
    var id = element.SelectSingleNode( "//id" ).InnerText;
    var type = element.Attributes[ "Type" ].Value;
}

via XLinq

XDocument xDocument = XmlHelper.GetRawSnippetAsXDocument();

var item = from doc in xDocument.Descendants( "subscriber" )
           let status = (string)doc.Element( "Status" )
           where status == "InActive"
           select doc;

foreach ( XElement xElement in item )
{
    var id = xElement.Element( "id" ).Value;
    var type = xElement.Attribute( "Type" ).Value;
}

Now here is an example where I think the XLinq syntax is cleaner and easier to understand. There is no need to know or understand the XPath syntax, which can be a pain to learn. Also here you see how XLinq looks a lot like standard SQL syntax and should be familiar to most developers.

Well, above I have shown you a few simple examples that compare Xml/Path to XLinq, I hope you find this useful. The next post will compare how to update/add data to a document using the both Xml/XPath and XLinq.

Till next time,

Using XLinq to manipulate an XML Document, XLinq U Rock

Derik Whittaker — Thu, 01 May 2008 21:26:40 GMT

For the first time in a long time I needed to update values inside an XML document. I have done this type of action many times in the past and have always hated it. The syntax is clunky and the process is simply painful.

So today when I realized I need to do this, let me just tell you I was NOT looking forward to writing this code. Then I remembered that with .Net 3.5 they added XLinq and it allows you to simply navigate and manipulate XML documents. This was exactly what I was looking for.

In order to figure out how to use XLinq to manipulate a file I turned to the book Linq in Action to find the answer. In the very short time I have had this book, I have found it to be very useful.

Ok, ok, enough chatter, lets get to the problem and how I solved it.

For my task, I need to open a XML Document that is stored as an embedded resource in our application, make changes to it and pass it along as an XML Document. The end goal is to take this document and sent it along to one of our 3rd party vendors via the XML Api.

Here is the Raw XML that needed to be modified

<?xml version="1.0" encoding="utf-8" ?>
<root>
<authorization>
    <username></username>
    <password></password>
</authorization>
<call>
    <call_name></call_name>
    <call_action></call_action>
    <search_type></search_type>
    <search_value></search_value>
    <search_value2></search_value2>
</root>
</root>

Below is the code needed to update this document with the provided values.
In order to use XLinq you need to import System.Xml.Linq

{
    XmlDocument xmlDocument = METHOD TO LOAD XML DOCUMENT HERE

    // Convert to an XDoc so we can process this
    XDocument xDocument = XDocument.Parse( xmlDocument.OuterXml );

    XElement authorizationElement = xDocument.Element( root" ).Element( "authorization" );
    authorizationElement.SetElementValue( "username", "SomeValue" );
    authorizationElement.SetElementValue( "password", "SomeValue" );

    XElement systemElement = xDocument.Element( "root" ).Element( "call" );

    systemElement.SetElementValue( "call_name", "SomeValue" );
    systemElement.SetElementValue( "call_action", "SomeValue" );
    systemElement.SetElementValue( "search_type", "SomeValue" );
    systemElement.SetElementValue( "search_value", "SomeValue" );

    // convert the xDocument back to an XML document for later usage
    xmlDocument.LoadXml( xDocument.ToString() );
    return xmlDocument;
}

So as you can see I can very easily and very quickly manipulate this document with the correct values. And because the syntax is in a fluent style it is VERY easy to read.

Till next time,

Multi-Column Grouping with Linq2Sql

Derik Whittaker — Wed, 30 Apr 2008 12:32:05 GMT

Today as I was diving further into my Linq2Sql Odyssey I ran into a need to do grouping on data. Bud not just single column grouping, I needed to group by multiple columns from multiple tables. Because it took me a few minutes to get right, I thought I would share my experiences.

Imagine if you will you have the following SQL statement

SELECT  COUNT(*) AS Count,
        cs.SendID,
        cs.Name AS SendName,
        cts.ListID,
        cts.ListName,
        ctom.EmailAddress
FROM    CampaignTrackingOpenedMail AS ctom
        INNER JOIN CampaignTrackingSummary AS cts 
			ON ctom.CampaignTrackingSummaryID = cts.[ID]
        INNER JOIN CampaignSend AS cs 
			ON cts.SendID = cs.SendID
GROUP BY cs.SendID,
        cs.Name,
        cts.ListID,
        cts.ListName,
        ctom.EmailAddress

And you would like to turn that into a Linq Statement. At first I was a little stumped because I was not using the correct syntax. My first attempt had me trying the following

from ctom in CampaignTrackingOpenedMails
join cts in CampaignTrackingSummaries on ctom.CampaignTrackingSummaryID equals cts.ID
join cs in CampaignSends on cts.SendID equals cs.SendID
group ctom by cs.SendID, cs.Name, cts.ListID, cts.ListName, ctom.EmailAddress into emailItems
select new { SendID = emailItems.Key.SendID,
			SendName = emailItems.Key.Name,
			ListID = emailItems.Key.ListID,
			ListName = emailItems.Key.ListName,
			EmailAddress = emailItems.Key.EmailAddress,
			Count = emailItems.Count()
			}

After a bit of searching I realized the error of my ways. I needed to use an anonymous type as my group by object value. So I made the changes and here is my correct attempt

from ctom in CampaignTrackingOpenedMails
join cts in CampaignTrackingSummaries on ctom.CampaignTrackingSummaryID equals cts.ID
join cs in CampaignSends on cts.SendID equals cs.SendID
group ctom by new { cs.SendID, cs.Name, cts.ListID, cts.ListName, ctom.EmailAddress } into emailItems
select new { SendID = emailItems.Key.SendID,
			SendName = emailItems.Key.Name,
			ListID = emailItems.Key.ListID,
			ListName = emailItems.Key.ListName,
			EmailAddress = emailItems.Key.EmailAddress,
			Count = emailItems.Count()
			}

A few things to notice here are:

The use of anonymous types
group ctom by new { cs.SendID, cs.Name, cts.ListID, cts.ListName, ctom.EmailAddress } into emailItems
How the group by columns are used
select new { SendID = emailItems.Key.SendID ......

Well, hope this helps someone else.

BTW, If you are NOT using LinqPad for your Linq2Sql stuff, you really need to check it out.

Till next time,

Linq2Sql - Explicit Construction of Entity Exception

Derik Whittaker — Fri, 25 Apr 2008 15:38:27 GMT

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,

Anonymous Casting with Linq to Sql

Derik Whittaker — Wed, 09 Apr 2008 22:39:35 GMT

***** Disclaimer ******
I may be the last person to figure this out, but oh well.
***** End Disclaimer ******

Today as I was further exploring the coolness that is Linq to Sql I ran into a little situation. I was creating a query where I wanted to use the columns form multiple tables to build an object. Since the results did not map 1 to 1 from a table to a object I figured I would have to do it the mapping the hard way. Here was the code I originally had.

var results = from c in DBContextInstance().Companies
              join pc in DBContextInstance().ProgramControls on c.CoID equals pc.CoID
              select new {c.CoID, c.CoDesc, pc.ProjectNotes, pc.ProjectManager};

List newList = new List< HybridObject >();

foreach ( var result in results )
{
    HybridObject entity = new HybridObject
                              {
                                  CompanyID = result.CoID,
                                  CompanyDesc = result.CoDesc,
                                  ProjectNotes = result.ProjectNotes,
                                  ProjectManager = result.ProjectManager
                              };
    newList.Add( entity  );
}

After looking at the code above for a few minutes, I really, really did not like it. I knew there had to be a better way. So I started playing around with various ways to do this. I had a hunch that since I was creating a return value as an Anonymous type, I should be able to create a concrete type in its place. Sure enough, I could. With a little help from constructor initializers (sure you could also do this by creating a constructor that took in the values as well). Take a look at the new, cleaner code below.

var results = ( from c in DBContextInstance().Companies
              join pc in DBContextInstance().ProgramControls on c.CoID equals pc.CoID
              select new HybridObject
                         {
                                CompanyID = CoID,
                                CompanyDesc = CoDesc,
                                ProjectNotes = ProjectNotes,
                                ProjectManager = ProjectManager
                         } ).ToList();

Now if you ask me, the new code is much more concise and much simpler. It also does not waste CPU cycles, which is always a plus.

If anyone has a better way, please let me know.

Till next time,

Linq and Delayed execution

Derik Whittaker — Mon, 07 Apr 2008 18:11:57 GMT

If you have not taken a look at Linq (either Linq to objects or Linq to sql) you need to do so. I would say it is the coolest new feature that was released with .Net 3.5. But there is one thing that you MUST keep in mind when using Linq. It has been designed to perform lookups using delayed execution. When you declare your Linq statement all you are doing is creating the memory representation of the query as an express tree. The execution of this plan will NOT be executed until the first time you attempt to access the result set of the Linq query.

But before we get started I want to show the demo object model I will be using for this post.

public class Demo
{

    public string Name { get; set;  }
    public string Text { get; set; }
    public Int32 Count { get; set;  }

    public static List BuildDemoList()
    {
        return new List< Demo >
                   {
                       new Demo {Name = "First", Text = "Some", Count = 123},
                       new Demo {Name = "Second", Text = "Other", Count = 223},
                       new Demo {Name = "Third", Text = "Value", Count = 443},
                       new Demo {Name = "Fourth", Text = "Goes", Count = 224},
                       new Demo {Name = "Last", Text = "Here", Count = 5534}
                   };
    }
}

Lets take a look at a few examples to help demonstrate how Linq's delayed execution works

This first block simply shows how you can query a collection and spit out the contents

List< Demo > values = Demo.BuildDemoList();
var result = from d in values
             where d.Count >= 224
             select d;

foreach ( var demo in result )
{
    System.Diagnostics.Debug.WriteLine( string.Format( "{0}, {1}, {2}", demo.Name, demo.Text, demo.Count ) );
}

// output
// Third, Value, 443
// Fourth, Goes, 224
// Last, Here, 5534

This second block does the same as the ones above, but with a twist. Because we are changing the value of an item in the list PRIOR to using it we are going to change the results.

List values = Demo.BuildDemoList();
var result = from d in values
             where d.Count >= 224
             select d;

values[ 2 ].Count = 100;

foreach (var demo in result)
{
    System.Diagnostics.Debug.WriteLine(string.Format("{0}, {1}, {2}", demo.Name, demo.Text, demo.Count));
}

// Output
// Fourth, Goes, 224
// Last, Here, 5534

This final block appears to be the exact same as the second above, but with one small change. We have called the .ToList() on the Linq statement. By calling the .ToList() we are forcing the execution of the query immediately. Now when we change a value in the underlying collection and then iterate over the results we will NOT get the changes.

List values = Demo.BuildDemoList();
var result = ( from d in values
             where d.Count >= 224
             select d ).ToList();

values[2].Count = 100;

foreach (var demo in result)
{
    System.Diagnostics.Debug.WriteLine(string.Format("{0}, {1}, {2}", demo.Name, demo.Text, demo.Count));
}

// Output
// Third, Value, 100
// Fourth, Goes, 224
// Last, Here, 5534

The thing I would like to stress here is that Linq by default will NOT execute the query prior to the first use. I know this will cause issues for some, and may be very hard to spot if you do not understand this fact.

Till next time,

Using Linq to access the file system

Derik Whittaker — Sun, 30 Mar 2008 21:31:14 GMT

Again, this post is another in the recent string of How-to's. But it is just so hard to resist. .Net 3.5 has so many cool features, it is just fun to explore them all.

In today's post I thought it would be fun to use Linq to access the file system. I thought I would show how to query both for folders and for files. In each example I will give the non-Linq way and the Linq way.

Searching for a giving Directory (by name)

Non-Linq way

FileInfo[] files = new DirectoryInfo( @"K:\SomeFolder" ).GetFiles();

foreach ( FileInfo file in files )
{
        if ( file.Name == "SomeFileName.txt" )
	{
        	have a match, do something now
	}
}

Linq way

var files = from file in new DirectoryInfo(@"K:\SomeFolder").GetFiles()
	where file.Name == "SomeFileName.txt"
        select file;

Searching for a given File (by name)

Non-Linq way

DirectoryInfo[] di = new DirectoryInfo( @"K:\SomeFolder" ).GetDirectories();

foreach ( DirectoryInfo directoryInfo in di )
{
        if ( directoryInfo.Name == "SubFolderName")
	{
		// have a match, do something now
	}
}

Linq way

var folders = from folder in new DirectoryInfo(@"K:\SomeFolder").GetDirectories()
	where folder.Name == "SubFolderName"
        select folder;

The above Linq code is not better or worse really, just different. And like I said before, if you are going to be using .Net 3.5, might as well use the new features.

Till next time,

Sorting List's the .Net 3.5 Way

Derik Whittaker — Sat, 29 Mar 2008 02:09:13 GMT

This post is another in the recent string of How-to's. But it is just so hard to resist. .Net 3.5 has so many cool features, it is just fun to explore them all.

Ok, so here is the issue we are going to solve today. We have a list of entities and we need to sort them. In this post I will show you 3 different ways to accomplish this task, all producing the same result, but using different techniques (hey Peter, go ahead and show me a 4th and better way :) ).

A little up front declarations. Each method will use the same list, so to save space I will declare this now.

List userList = new List
    {
        new User {FirstName = "Andy", LastName = "Tayler"},
        new User {FirstName = "Opie", LastName = "Tayler"},
        new User {FirstName = "Bee", LastName = "Tayler"},
        new User {FirstName = "Barney", LastName = "Fife"},
        new User {FirstName = "Goomer", LastName = "Pile"}
};

Sort Method 1 -- Using the Sort with IComarer

public void Sort_OldSchool()
{
    userList.Sort(new UserCompare());
}

// Sort class
private class UserCompare : IComparer<User>
 {
    public int Compare( User x, User y )
    {
    return x.LastName.CompareTo( y.LastName );
}

The above works, but requires you to create a custom class that implements the IComparer<User> interface to do the trick.

Sort Method 2 -- Using Lambda and extension methods

[Test]
public void Sort_UsingLambda()
{
	userList = userList.OrderBy( user => user.LastName ).ToList();
}

This way works great, it is clean and very readable.

Sort Method 3 -- Use Linq2Objects

[Test]
public void Sort_UsingLinq()
{
	userList = ( from u in userList
        	orderby u.LastName, u.FirstName
                select u ).ToList();
}

By using Linq you open yourself up to a whole new level of flexibility. You can not only sort (OrderBy), you can filter, return only a select number of rows, etc, etc. Basically you can do anything that Linq provides.

As you can see from the above, they all accomplish the same goal but method 2 & 3 are just more fun. Besides, if you are going to be using .Net 3.5 you might as well use the new features.

BTW, all the methods (except 3 since it sorts by LastName and FirstName) above produce the following output.
Barney Fife
Goomer Pile
Andy Tayler
Opie Tayler
Bee Tayler

Till next time,