I’m pretty excited about two upcoming events coming to our community and I have an active role in both of them. 

First, I’ll be presenting at the .Net Data Access Firestarter on March 17, 2010.  This event, hosted at the Microsoft Office in Malvern, PA covers a variety of .Net data access strategies including LINQ to SQL, WCF Data Services and OData, Entity Framework, and even Azure Data Storage.  It’s a pretty good variety of information.  I’ll be doing one of the earliest sessions on the day, my topic is “Introduction To LINQ”.  I’m excited about this because LINQ is such an important part of the .Net Framework now.  While it isn’t really a data access technology, most of the data access technologies use LINQ!  It should be a lot of fun and I am honored to be a part of the event.  Also, if you can’t make it out to the event, you can watch it online too!

Here is a link to more information.

Second is of course, Philly.Net Code Camp.  Our next Code Camp is April 10, 2010.  Once again we are back at DeVry University.  These Code Camps just continue to get better and better.  Once again, we 60 sessions in a variety of technologies.  In addition, we are broadcasting a portion of the content via Live Meeting and they’ll also be available for download later.  That alone makes this an exciting event.  This year, we’ve also invited some folks from Alex’s Lemonade Stand to come by.  At the end of the day, we’ll be presenting them with a donation to their worthwhile organization.  How are we raising the money?  This year we are offering two kinds of tickets to Code Camp.  The first is the standard, free ticket.  Anyone is welcome to come to Code Camp for free and enjoy the day (and breakfast and lunch are included as always).  But we have added a Booster ticket this year for $25.  If attendees choose to donate this small amount, they get a few benefits:

  • $5 of the each Booster ticket goes to Alex’s Lemonade Stand
  • Boosters are included in some premium raffles
  • Boosters are included in the post Code Camp party
  • Booster money will help sustain Philly.Net throughout the year.
  • Other benefits are included as well.

This is sort of an experiment.  The leadership, of which I am a member, doesn’t know how this will turn out.  It seems to me that $25 is a small price to pay for a ton of content at Code Camp, not to mention the fact that we provide breakfast and lunch.  And we hope that people won’t mind donating to our efforts, it takes a lot of money to run the organization and all of the events year round.  Plus, we are donating a portion to charity.  On the other hand, I always liked these events being free.  People donate their time so that others can come and learn for free.  It’s a great concept!  But times are changing and we need money to run our organization year round.

In any case, whether you choose to donate or attend for free, I hope to see you at Code Camp.

If you are working with LINQ to SQL, you may be familiar with this exception message:

“Cannot add an entity with a key that is already in use.”

This typically comes up when you are working with LINQ to SQL, as I am, in a “disconnected ” situation.  In my case, I am working with a Silverlight Application, so I get some data with one DataContext, then pass it out to my Silverlight application (this is the same with any other web application), make changes to it, pass it back to the server where it gets updated using a different instance of my DataContext.

The interesting thing in my case is that I thought I fixed the problem but I still got the exception.  I’ll explain why.  But first, there are several ways you can resolve this issue.  How you choose to fix this will have to do with how your database is set up initially.  I am working with an existing database and there are limits to how I can change the db.  So I have my all of the properties in my LINQ to SQL Entities set as “UpdateCheck = UpdateCheck.Never”.  Next I’ll “Attach” my entity to my DataContext  but before I do that, I’ll “Detach” it  My Detach() method “resets” all of the EntitySets and EntityRefs connected to my entity so that LINQ to SQL doesn’t think I am trying to insert them into the database.  Sorry, I’m not going into details on this process in this blog post but you can easily find details on the web.  Now I can attach my disconnected entity to my DataContext like this: 

            using (ABCDataContext db = new ABCDataContext())
            {
                foo.Detach();
                db.Foos.Attach(foo, true);
                db.SubmitChanges();
            }

This should work fine.  But it wasn’t working in my case.  Why?  It is a typical case of how various steps I took to resolve the a different problem ended up causing new problems.  I was debugging and trying a bunch of stuff because my calls to Attach were throwing exceptions.  There is an overload to the Attach() method that would look like this — db.Attach(newEntity, originalEntity)  — so that LINQ to SQL can compare the two items.  I had been playing around with it but when I commented out my code, I forgot to comment out one line, so it looked like this:

            using (ABCDataContext db = new ABCDataContext())
            {
                foo.Detach();
                Foo oldFoo = db.Foos.SingleOrDefault(f => f.FooId == foo.FooId);  // NOT REMOVING THIS LINE CAUSED THE EXCEPTION!
                //db.Foos.Attach(foo, oldFoo);
                db.Foos.Attach(foo, true);
                db.SubmitChanges();
            }

When I run this, I get the exception “Cannot add an entity with a key that is already in use”.  For some reason, even though I wasn’t working with “oldFoo”, LINQ to SQL didn’t like the fact that I requested it from the database.  So I commented out that line (Foo oldFoo = db.Foos.SingleOrDefault…) and everything just started working fine. 

It was a pain but it is over now.  Now it is time to solve more problems…

Tonight at Philly.Net we are having our annual “15 Minutes of Fame” meeting.  That is where we have 10 of our regular presenters do a short 15 minute presentation on something they think is cool.  We’ve got a great line up planned for tonight and I am excited to not just present my topic but also learn a few things.  These mini presentations give the audience just enough to get started, plant a seed, wet your whistle, etc. 


It’s not always so easy to come up with an idea for such a short presentation.  I thought LINQ Compiled Queries was a good one because, while it is a big feature of LINQ, it is relatively easy to implement.  I also think people avoid it because it sounds complicated.  In fact, it is not. 


I’ll demonstrate with some code taken from this VS2008 solution.


Here is a regular method that takes a few parameters, executes a “SELECT” on the Northwind Customers table, and returns a list of Orders.  You’ll also note that there are parameters to support paging, a common scenario with LINQ.


public static List<Order> GetOrdersNotCompiled(string custName, string shipCountry, int skip, int take)
{
//even though the query is compiled, you can still pass in parameters.
using (NorthwindDataContext db = new NorthwindDataContext())
{
List<Order> orders = (from order in db.Orders
where order.Customer.CompanyName == custName
&& order.ShipCountry == shipCountry
select order).Skip(skip).Take(take).ToList();

return orders;
}
}


So how to convert this to a Compiled Query?


A Compiled Queries get stored as a Static Func<>.  A Func acts as a “pointer” to a method.  We make it static so it can be reused across the application.  That is how we get the performance gain.  Once the .Net framework figures out how to take the LINQ and turn it into a SQL statement, it gets saved for reuse in the Static variable.


A Func<> has a limited number of parameters that can be passed to it.  I like the technique of standardizing what gets passed in, passing the datacontext in the first parameter and a “helper class” containing all other parameters as the second.  And of course, last is the out parameter.


Here is our helper class:

private class GetOrdersInputs
{
public string CustomerName { get; set; }
public string ShipCountry { get; set; }
public int Take { get; set; }
public int Skip { get; set; }
}

Now let’s look at the Func<> declaration:

private static Func<NorthwindDataContext, GetOrdersInputs, IQueryable<Order>> GetOrdersQuery =
CompiledQuery.Compile((NorthwindDataContext db, GetOrdersInputs inputs) => LINQ GOES HERE );

You can see that the Compile method takes a lambda expression.  I thought it was easier to follow without the complete LINQ.  Here is the complete version:

private static Func<NorthwindDataContext, GetOrdersInputs, IQueryable<Order>> GetOrdersQuery =
CompiledQuery.Compile((NorthwindDataContext db, GetOrdersInputs inputs) =>
(from order in db.Orders
where order.Customer.CompanyName == inputs.CustomerName
&& order.ShipCountry == inputs.ShipCountry
select order).Skip(inputs.Skip).Take(inputs.Take));

Don’t worry if the Func<> seems confusing.  You’ll find it very easy to use over and over again, just replace the variables and the LINQ statement anytime you need it.


Now you just call GetOrdersQuery as follows:


public static List<Order> GetOrdersCompiled(string custName, string shipCountry, int skip, int take)
{


    // load up the helper class with parameters.
    GetOrdersInputs inputs = new GetOrdersInputs { CustomerName = custName, ShipCountry = shipCountry, Skip = skip, Take = take };

    // even though the query is compiled, you can still pass in parameters.
   
using (NorthwindDataContext db = new NorthwindDataContext())
    {


        // Execute the compiled query.
        List<Order> orders = GetOrdersQuery(db, inputs).ToList();
        return orders;
    }
}


Results:  The included demo is a little Console App that runs each query 27 times.  I’m using a free tool – CLR Profiler from Microsoft to analyze my application.


The compiled query executes about 257 thousand method calls (that is .net converting the LINQ into SQL).  But the non compiled version executes about 2.6 million calls because it does the work over and over again!


CLR Compiled


 


The Compiled Query will perform much better and it really wasn’t much work to implement.  So give it a try!


Again, here is the source code:  CompiledQuery.zip


Technorati Tags: ,

Philly.Net Code Camp was great.  We had over 400 people attend and the feedback is very good.


I had a great time with my presentation “Make the Switch to LINQ – Working with data will never be the same”.  Thanks to everyone who joined me.  It was pretty crowded.  Thanks to those of you who stood up or sat on the floor.  I know that I covered a lot of information in my session.  I hope I didn’t go to fast.  I promised that I’d have my Code Camp presentation up on my blog by today.  I’m running a little behind schedule.  Update:  here it is!  On schedule, even if it is 10:30 at night!


I’ve included a db create script, all of my source code, and the slides.  I have tried to add more comments to the code so it makes sense.  At the end I was pretty rushed and didn’t explain all of the samples but I hope they speak for themselves.


If you have any questions, just ask… you can contact through the link on my blog.  I’ve also included my address in a ReadMe.txt doc in the zip file.


LINQ Demo.zip (900.4 KB)


Now, make the switch to LINQ!