Adding Custom Property to an Entity in Entity Framework

Let’s assume we have the following scenario.  We have a Person entity in the Entity Framework Model and it a relationship to a Pet entity. Assume the Pet entity has a property called PetName that we want to show on the Person detail, edit, grids, etc. The easiest way is to add a property to the Person entity called PetName. The trick is how to populate it and how to save any changes that users make to it. You can use this same technique (described below) regardless of whether the data you are pulling into the Person table is in your model, entity, etc.

Yes, I understand this example is not really a great example. The model really isn’t that great, but I hope you get the idea.

The first thing to know is that for any entity in your Entity Framework Model you can extend that object by creating a partial class that is in that entities same namespace and classname. Here is our new partial class.

namespace
{
public partial class Person
{
private string _PetName;
public string PetName
{
get
{
string val = sting.Empty;
if (!string.IsNullOrEmpty(_PetName)) val = _PetName;
else
{
if (this.Pet != null)
{
val = this.Pet.PetName;
}
_PetName = val;
}
return val;
}

set { _PetName = value; }
}

 

Notice that we populate the PetName property in a lazy load fashion. Meaning, we don’t go get the data until it is requested. If you use Include(“Pet”) when you get the Person object then the Pet object will be in memory already and not a separate round-trip to the database for each person that is accessed. This query would typically be in your Domain Service and might be called something like GetPersons() or GetPeople() if you renamed it.

This great for displaying information. Note that you don’t need the Set portion of the PetName property if you are just displaying the information and not editing it.

Adding Editing Functionality

To handle the editing we need to go to the Domain Service. Here you should have a UpdatePerson() and InsertPerson() methods. In each of these methods we want to do something to the Pet entity or even call a stored procedure, etc. In our case the the Pet entity is what we want to update so I’ll show you how to do that. Be sure to do the update AFTER the AttachAsModified call otherwise the Load() method call will fail because the currentPerson is not in the ObjectContext.

public void Person(Person currentPerson)
{
this.ObjectContext.People.AttachAsModified(currentPerson, this.ChangeSet.GetOriginal(currentPerson));
if (!currentPerson.PetReference.IsLoaded) currentPerson.PetReference.Load();
if (currentPerson.Pet != null)
{
currentPerson.Pet.PetName = currentPerson.PetName;
}
}

 

Limitations

This is actually pretty easy as you can see. One thing to note about adding properties in general is that you will get a runtime error if you try to use property we added in a Linq To Entities query. This means that filters and sorting by clicking on column headers on the GridView generally won’t work without some extra works that changes the property to the path it actually maps to. In this case it is possible, but in other cases such as stored procs being called, this mapping would not be possible.

Create CRUD UI using ASP.NET 4.0, FormView, Dynamic Data, DomainDataSource, Entity Framework, WCF Domain Service, LINQ, and custom validation – Part I

This is one entry in a series of blog entries:

• Create CRUD UI using ASP.NET 4.0, FormView, Dynamic Data, DomainDataSource, Entity Framework, WCF Domain Service, LINQ, and custom validation - Part I
• Create CRUD UI using ASP.NET 4.0, FormView, Dynamic Data, DomainDataSource, Entity Framework, WCF Domain Service, LINQ, and custom validation - Part II
• Why aren’t my ForeignKeys being loaded on my Custom Dynamic Data page?

The title is a mouthful and thus the topic can be kind of overwhelming. With ASP.NET 4 there are so many different technology frameworks that you need to be familiar with. The problem that I have found is that most examples only use a one or two of these frameworks. In my world, I want to use them all and the examples are never as simple as the examples. In this blog entry, I will give a read world example of how to combine all these technologies. I find myself having to figure out how to do this over and over again. Hopefully, this will be of help to others.

This blog entry is NOT about how to create a Dynamic Data project in VS2010 and create an admin UI or something like that. This is about creating a page from scratch that uses this technology. To better understand the different scenarios that Dynamic Data can be used in and steps on how to extend it, see here.

Technology Overview

I am writing in the context of ASP.NET 4.0 and VS2010 (Visual Studio 2010). These technologies are relatively new in some cases and the api’s have changed since betas. I am only showing stuff that works in VS2010 (the final released version).

• ASP.NET 4.0 – This is the version of ASP.NET that I am targeting.
• FormView – This is the control that we will be using as a basis for the CRUD UI (User Interface).
• Dynamic Data – This is the technology that allows you to use DynamicControl on your FormView instead of the standard ASP.NET Forms controls. BTW, you don’t have to have anything installed (not even the Dynamic Data files and templates). You get basic implementation without them, but with them you can customize and get a more full-featured set of tools. See here for a GREAT video on using Dynamic Data in “old” applications that don’t have all these cool technologies. For the most up to date resources, click here.
• Entity Framework 4.0 – The standard with VS2010 for database to object mapping. Click here if you want to see how to use Dynamic Data with an ObjectDataSource and GridView.
• WCF Domain Service – This is your middle tier where you would put your business logic, complex validation, etc. It provides a consist way to access your perform CRUD operations on your data. Abstraction layer before the Entity Framework.
• LINQ – We will use LINQ to Entities in the WCF Domain Service to query database through the Entity Framework.
• DomainDataSource – This is a control that works much like the ObjectDataSource or LinqDataSource or EntityDataSource except that it can connect to your WCF Domain Service from ASP.NET. Please note that there is also one for Silverlight and it has the same name so when you are Googling be sure to check what the context is. This is a good doc on using this control.

Getting Started

You can select most any type of web application project in VS2010 to get started. My instructions will be for the Dynamic Data project so that I don’t have to explain how to manually move over the Dynamic Data files. If you have an existing project and need to copy over the Dynamic Data files, click here for detailed instructions. Please note, I have copied some of these steps (and written others) from instructions I have read on MSDN, but have combined them together in one continuous instruction set. For example, most of the instruction are from: here and here.

1. Open Visual Studio 2010 and create a new project of type ASP.NET Dynamic Data Domain Service Web Application. I’m calling my project MyDynamicDataExample.
2. Optional - Create two Directories: Models and Services.
3. Optional - Download and install the AdventureWorksLT database if you don’t already have it. The particular database is not really that important to understand the concepts. Notice I am using the Lite version since it is a bit simpler to follow, etc.
4. Add a New Item… and select ADO.NET Entity Data Model. I called MyModel.edmx. We will generate from Database. Select the database you want to work with (add a new connection if it is not there). On the Choose Your Database Objects screen, select Product and ProductCategory. Keep everything else default.
5. You must build your project so that the next step will work.
6. Add (to Services folder) a New Item… and select Domain Service Class. I called mine AWDomainService.cs. Select the options as shown below:
   
7. Add (to Models folder) a New Item… and select Web Form using Master Page. Call it DDTest.aspx. Select the Site.master on the next screen.
9. From the General or Data group of the Toolbox, add a DomainDataSource control to the page. If you have never used the DomainDataSource before you will need to add it to your toolbox. To add it to your toolbox. Click the Tools menu, and then click Choose Toolbox Items. In the Choose Toolbox Items dialog box, click the .NET Framework Components tab, select the DomainDataSource check box, and then click OK.
10. Add a DomainDataSource to the page. Configure the datasource to point to GetProducts() method on the service you created. Enable Inserts, Updates and Deletes.
11. Add a QueryExtender to the page. Set the TargetControlID to the id of the DomainDataSource (mine is called DomainDataSource1).
12. Add a FormView (though a GridView or DetailsView could also be used). Check the Enable Dynamic Data Support checkbox. Please note it will not stay checked when you come back to it later (in some cases). Set the Data Source to the DomainDataSource you added earlier.

13. Add a DynamicDataManager control to the page. Chose Register Controls… and add the FormView as shown below.
    
    This registers the data-bound control and enables dynamic behavior for it.

14. Open the code-behind. Add a Page_Init method and add the following line:
    

    protected void Page_Init(object sender, EventArgs e)
    {
        FormView1.EnableDynamicData(typeof(MyDynamicDataExample.Models.Product));
    }

15. Go to the .aspx page again and go to the Source view. Find the QueryExtender you added earlier. Add the following line to it:
    

    <asp:QueryExtender ID="QueryExtender1" runat="server" TargetControlID="DomainDataSource1">
            <asp:DynamicRouteExpression ColumnName="ProductID" />
    </asp:QueryExtender>
    
    This will allow us to pass the ID that we want to edit via the url as a query string.

16. Now we need to do some clean up. In each of the templates in the FormView remove the Text and Dynamic controls for: ProductCategoryReference, EntityState, and EntityKey. Also, remove the rowguid from the insert template.

17. Let’s test to see if this works now. You will see that you get the YSOD (Yellow Screen Of Death) when you click the Edit button and then the Update button. You will get an EntityOperationException and the last method that was called was HandleValidationErrors. This means that something in the model failed.

18. Create event handlers on the DomainDataSource for the Updated and Inserted events. They should look like this when you are done:
    

    protected void DomainDataSource1_Inserted(object sender, Microsoft.Web.UI.WebControls.DomainDataSourceStatusEventArgs e)
    {
        if (e.ChangeSetEntry.HasError)
        {
            foreach (var error in e.ChangeSetEntry.ValidationErrors)
            {
                AddValidationSummaryItem(error.Message);
            }
        }
    }

    protected void DomainDataSource1_Updated(object sender, Microsoft.Web.UI.WebControls.DomainDataSourceStatusEventArgs e)
    {
        if (e.ChangeSetEntry.HasError)
        {
            foreach (var error in e.ChangeSetEntry.ValidationErrors)
            {
                AddValidationSummaryItem(error.Message);
            }
        }
    }
    

19. Next add this supporting method.
    

    public void AddValidationSummaryItem(string errorMessage)
    {
        var validator = new CustomValidator();
        validator.IsValid = false;
        validator.ErrorMessage = errorMessage;
        this.Validators.Add(validator);
    }

20. Now put a breakpoint on the Updated event handler and debug your application. Do the same test again, you’ll see there is some error about the field: ThumbnailPhoto. Note that the error does not display on the page, but it doesn’t go to the YSOD either. Like any other validation exception that is NOT column specific, we need to use the ValidationSummary control to view this error.

21. Drag a ValidationSummary control to your page. Re-run and you’ll see the exception. This does give little bit of database schema information. This may be considered by some to be bad. I’ll leave that up to you. If you don’t do this then you won’t get validation exceptions that are thrown from your domain service which is how you will implement your business logic. My experience shows that nothing too serious comes out of here once you have everything wired up correctly. If you don’t like that you’ll have to come up with another solution.

22. Let’s quickly fix this by removing the ThumbNailPhoto field from each of the FormView templates. Re-run, and this time you won’t get any errors. We can do this because it is an optional field.

23. Let’s add some validation to see how to do that. Go to the AWDomainService.cs and find the UpdateProduct method. Modify it so that it looks like this:
    

    
    public void UpdateProduct(Product currentProduct)
    {
        this.ObjectContext.Products.AttachAsModified(currentProduct, this.ChangeSet.GetOriginal(currentProduct));
        if (currentProduct.ListPrice < 2000)
            throw new ValidationException("List Price is too low.");
    }
    Re-run, change the List Price to less than 2000 and update and you’ll get the message shown in the ValidationSummary control.

24. You can also use ValidationAttributes such as Required, Range, RegularExpression, etc in the Model to declaratively validate the data. To do this open your AWDomainServices.metadata.cs. Find the StandardCost property and modify it so that it looks like this:
    

    [Range(0, 200, ErrorMessage="{0} must be between 0 and 200.")]
    public decimal StandardCost { get; set; }
    
    Re-run and and change the standard cost to something greater than 200 if it isn’t already and Update. You’ll see it says StandardCost must be between 0 and 200. Notice it filled in the name of the field for us. It is the actual property name though.

25. To display a user-friendly version of the property name you can add Display information as shown below.
    

    [Display(Name="Standard Cost")]
    [Range(0, 200, ErrorMessage="{0} must be between 0 and 200.")]
    public decimal StandardCost { get; set; }
    
    Re-run and you’ll see the same message but with the friendly name of the property.

26. To Edit a particular record such as the Product with ID = 800, just go to the same url, but with DDTest.aspx?ProductID=800.

27. If you try to run your application without specifying the specific test page you will get an error like this “There are no accessible tables. Make sure that at least one data model is registered in Global.asax and scaffolding is enabled or implement custom pages.”
    
    To fix that you just need to go to your Global.asax.cs and uncomment the line that start with DefaultModel.RegisterContext. Change it so that it looks like this:
    
    DefaultModel.RegisterContext(new DomainModelProvider(typeof(MyDynamicDataExample.Services.AWDomainService)), new ContextConfiguration() { ScaffoldAllTables = true});
    
    When you do this you’ll have some CRUD pages for each of the tables you included. This is separate from what we are doing here which is a custom page, but let’s fix the errors anyway. Another option would be to not have this default page and leave that line above commented. That way you won’t have all these admin page open to the world. Yes you can secure them, but they are not by default.
    

28. Back on track, when you access the Default.aspx page you will get an error like this: “The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.”
    
    To fix this just go to the AWDomainService.cs and add a sort method to the default query methods which are GetProducts and GetProductCategories. Just make the methods look like these:
    
    

    public IQueryable<ProductCategory> GetProductCategories()
    {
        return this.ObjectContext.ProductCategories.OrderBy(o => o.Name);
    }
    

    public IQueryable<Product> GetProducts()
    {
        return this.ObjectContext.Products.OrderBy(o => o.Name);
    }

29. Re-run. This time go to the Product link. The same validation we added before is also here. This is because the validation is written at the Model, not the UI. This is AWESOME in my opinion. I love it. This is why I love Dynamic Data.

I hope this has been beneficial to all.

The entire source for this example (minus the AdventureWorksLT database) can be downloaded from here.

AzGroups 8th Annual Scott Guthrie Event

Things to check out:

NUGET

(pronounced NEWGET) – it is built into VS2010 (Visual Studio 2010) and makes finding, downloading, and installing libraries (open source included) into your project. It even does some configuration in the config files, etc. Pretty awesome. You can get it here.

elmah

It is an error logging facility that is completely pluggable that can added to a running ASP.NET web application without re-compilation or deployment. When an exception occurs it logs it, but allows you decide where to log it to such as a database, RSS, email, etc. It also records A LOT of VERY useful information for determining what happened.  See here for more details.

IIS Express

Can be used side-by-side with the built in web server (Cassini) in VS2010. It launches fast just like the web server built into VS2010, but doesn’t require you setup virtual directories, etc like you do with IIS (full version). You now get best of both worlds. It now let’s you develop using the IIS 7.x feature-set. It is now as easy to use as the web server built into VS2010. It doesn’t require admin account. You don’t have to change any code or the way you work to take advantage of it. See Scott’s blog for more details.

Web Matrix

You should still continue to use Visual Studio for professional work, but Web Matrix project are great for hobby, family sites, etc. It has been totally reworked to be a task based tool. It makes publishing very easy. If the site gets more complex the project can be opened up in Visual Studio.

SQL Server Compact Edition 4

It is great for ASP.NET development. It is light weight and free. It is an embedded database (no SQL Server required) so it is in the binaries (bin directory) that you can xcopy to your server. You can use Standard SQL Server syntax so anything that supports the ADONET provider model (such as LINQ and EF) will work. You are also licensed to redistribute it. All this means you can now deploy this in a medium trust ASP.NET shared web hosting such as DiscountASP, GoDaddy, etc. This means you don’t have to pay for the SQL database. It integrates into VS2010 (coming soon). It is different from previous version because it is safe to use with multi-threaded environments (such as ASP.NET). and no longer blocked from being used with ASP.NET. The catch is that it is for light-usage production (4GB or less for datafile) usage scenarios and it does NOT support stored procedures. For high-volume usage usage, you’ll probably want to use SQL Server Express (which is still free), SQL Server, or SQL Azure. Also, since stored procs are not supported the ASP.NET Membership is not supported (though they will be shipping new providers so that they do). The only change to your code when you migrate is your configuration string. See Scott’s blog for more details.

 

Portable Library Project

It is a special kind of project in VS2010 that only allows code that will work on particular targets. In particular, the targets are Silverlight and Silverlight for Window Phone 7. Intellisense only lets you see Intellisense on methods, properties, classes, namespaces, etc that are compatible with both. The most I could find on it was here.

Silverlight 5

Great stuff coming with Silverlight 5. Better DataBinding support for MVVM. Improved Printing and Text. Finally there will be a 64-Bit version of Silverlight. 3D Modeling that use Hardware acceleration. 3D and 2D can be used together. Breakpoints on XAML Bindings.

MVC 3

I am really impressed with MVC3. I am very tempted to try a project with MVC3. It takes advantage of HMTL 5 including Data validation, round corners, etc.

MVC Basics

The basic idea is that a url is mapped to a controller and method on that controller. That method makes calls to the model where most of the business logic resides, and then typically returns a view which is responsible for rendering the response (page). It maps url to action methods on controller class. In general the best practice with MVC is to have skinny controllers and fat models. Default method is called Index() and is called when no action is specified. Can pass entities as parameters to methods; just need to map them. Default parameters can be used as well. (i.e. int num = 5). ASP.NET MVC forces separation, while ASP.NET Forms does not.

Routing

When determining which rule matches it starts with the one that was added first. It then proceeds down the list until one matches. Routing is used to map urls to controllers and actions, but it is also used to generate urls in the application.

Glimpse

It is a an open source tool that allows you to diagnose what is happening on the server, but from the client. It is very useful when trying to figure out what happened once your request made it to the server. For example, what route was executed. It is basically Fiddler for the server.

For more information see here.

Razor

It is a View Engine for MVC. For more info see ScottGu’s blog. Razor files are .cshtml. Tags start with @ and it automatically figures out where code ends and content begins. Content and code can be intermingled. A layout page is basically like a master page in ASP.NET Forms. You can use traditional parameters or Named parameters. Use Custom helpers for custom rendering; refactoring in a way.

MVCScaffolding package

It is a code generator for MVC; a smart one at that. Highly recommended so you don’t have to write so much code to do CRUD UIs. Use NuGet Package Manager to get and install it.

Entity Framework 4.1

Great code first support. You can use POCO objects, make changes and the database just updates. It is so cool. This behavior is optional, but very nice. You can setup database initialization code to populate some basic data when the database is dropped.

Modernizr

A great tool that allows you to take advantage of HTML 5 when it is available and downgrade when it is not. An example of this is round corners. Very nice tool that can be used in most any ASP.NET project. http://www.modernizr.com/

Windows Phone 7

Later this year there will be a new release called Mango. It is a very exciting release as it will bring the Windows Phone 7 much closer to the competition as far as its offering. To get started with Windows Phone 7 development go here. Everything that is developed for Windows 7 Phone falls into two categories: Silverlight or XNA. XNA is for games (also used for XBOX), and Silverlight is for everything else. You can import some XNA libraries (such as audio) into Silverlight. Tombstoning is basically storing state to appear like the application never quit when in fact it did. It basically works by providing a state bag that most anything can be written/read to when different events such as (navigate to and from are called). It works much like Viewstate does in ASP.NET except it is not automatic. So more specifically it works like adding and retrieving stuff to/from viewstate in ASP.NET. The cost is $99 per year to put your application (or as many as you want except not more than 15 free ones) in the Marketplace. You can register to developer unlock your Windows Phone 7. Visual Studio 2010 comes with an emulator, but the one that will be in the mango release is sooo much better because it simulates things like sensors. The talk was given by Jeff Wilcox.

Tips on Windows Phone 7 development

Change splashscreen (an image) to all black if you app launches immediately. That way it is perceived as faster and cleaner. If your app takes a few seconds to load then put in a custom splashscreen (change the image) to give a more professional look. Add scrollviewer or list so that scrolling will always be available if something doesn’t fit on the screen. Use the metro look and feel styles, don’t hard code styles. If things don’t align as expected, then use 12px margins. Promote your app by creating a site for it. Prompt a user for review of your app after you think they have used it for a while. Statistically most apps are only used once and never used again. Use Watson to collect crash information, bugs, etc. Use Google Analytics to track page navigation within your app. Keep your main dll as small as possible since the entire thing is checked before loading into memory. Delay load pages / dlls on an as needed basis to help accomplish this. This also helps with startup times. Use a tombstoning helper. Use caching whenever possible.

Windows Azure – by Scott Guthrie and Mark Russinovich

Windows Azure is a great technology that needs some work with regards to deployment speed, and in general ease of use, but it is extremely well architected for scaling your application in a very elastic way. It allows you to worry about your app and not the infrastructure it is running it. It is Microsofts offering of cloud computing. The idea is that if you need 100 servers for an event you have them. Then after the event you don’t need them. With traditional models you would have to have servers ready to go at all times and pay for that much power. Since Microsoft has a giant server farm called Windows Azure and a middle tier between you and the server OS you only pay for what you use for the length of time you use it. Everything is continually monitored for 99.? uptime.

Mark went into all the details or the architecture. He convinced me it is very complex and robust, but not so fun to listen to a discussion about it. way too much details for my interest level. However, here as some of the things I found interesting. SQL is actually much slower than the way Windows Azure does it. They actually have everything as read only. Once it is read the objects are de-serialized and used. If they need to update, they are written back to the cloud as serialized objects. This takes only a trillionth of a second instead thousandths of a second that SQL takes. This allows them to scale fast. Their belief is that in the next 10 years or so this will scenario will be common more so than the relational database. Currently options in Azure are SQL, Blobs, and Queries. I believe SQL Azure is part of Windows Azure.

Some Barriers / solutions to get over before cloud computing will be accepted are: Trust of proprietary data being outsourced; loss of control; confidence (ISO and other certifications will help); Private clouds may be an alternative; license to run in own data center may help also.

Export to Excel using Dynamic Data

If you are using Dynamic Data ASP.NET web application you are likely using the Entity Framework and a Domain Service. With these key technologies it is easy to add an “Export to Excel” link to all your List pages in your project. The best part is that once you add this, you don’t have to write it again for each table. This is a write once use for everything solution.

To implement the writing of data to Excel I don’t want to use anything that requires Microsoft Excel to be installed (except by the people that are downloading the file). I chose a free solution from CarlosAg.net called Excel Xml Writer. Click here to go to the product page and download it. It is implemented using C# and is a managed solution (no COM, interops, etc). The file that is generated is a xml version of the Excel file format so it is easy to debug and tweak if needed. Best of all the installation is simple: just add a reference to the single .dll. There should be no other dependencies.

I wanted to use the same column names that are in List page that I am exporting and I didn’t want to have to hard code them. I wanted them to be read from the MyDomainService.metadata.cs. If you use the DisplayAttribute to specify user friendly names, my code uses them instead of the column names. Also, not every property in the table are shown. It depends on what the MetaTable.GetScaffoldColumns() returns which is what the DefaultAutoFieldGenerator() for the List.aspx.cs page uses.

To encapsulate this logic, I created a class called PopulateExcelWorksheetForDynamicData.cs.  To download the source for the file click the filename. I’m not going to go through all the code step by step. However, the basic idea is that the Populate() method is called. You pass it an IQueryable object and the MetaTable information. Based on that the MetaTAble.GetScaffoldColumns() is called to determine what columns should be shown. For any foreign key columns I resolve to the related (called the parent table) and display the appropriate column based on the DisplayColumnAttribute or default DisplayColumn if one is not specified via the attribute.

The datatypes that are specified in the MetaTable are converted to Excel types. Special formatting such as DateTimes require a special format in order for Excel to know how to show them as dates. I make some assumptions about the format the user will want to see. This is hardcoded, but could be extended or changed.

You can tweak the formatting of the file as well. There are two formats I use. See: SetCannedStyle1(), SetDefaultStyle(). I haven’t used SetCannedStyle1() in a while, but I think it should still work.

That is the guts of the logic. Now it is just a matter of creating a button or a link on the List.aspx.cs page and executing code similar to the following.

protected void btnExportToExcel_Click(object sender, EventArgs e)
       {
           IQueryable queryResults = null;
           using (var service = new DistiPromoDomainService())
           {
               // execute the query that was used to populate the GridView.
               var selectMethodInfo = service.GetType().GetMethod(GridDataSource.QueryName);
               queryResults = selectMethodInfo.Invoke(service, null) as IQueryable;

  // add in any filters that are applied via the QueryExtender as well
   var queryExtenderExpressions = queryExtender.Expressions;

                foreach (DataSourceExpression expr in queryExtenderExpressions)
                {
                    queryResults = expr.GetQueryable(queryResults);
                }

               var workbook = new Workbook();
               var sheet = workbook.Worksheets.Add("Sheet 1");
               var excel = new PopulateExcelWorksheetForDynamicData(sheet);
               excel.Populate(queryResults, table);
               PopulateExcelWorksheetForDynamicData.SetDefaultStyle(workbook);
               // don't use spaces as this will cause issues. You can also try .xls, but you will get an corruption warning in IE.
               string filename = Title.Replace(" ", "-") + "-Export.xml";
               PopulateExcelWorksheetForDynamicData.SendToBrowserForDownload(workbook, filename, Context.Response);
           }
       }
You may want to tweak the filename. That should do it.

Using LINQ in a foreach loop to build a query

I love using LINQ because it makes building up queries so easy. The exception to this is when using a foreach with a union. I suspect it is just the foreach that is causing this behavior, but for sure the behavior is consistent when using both.

Let’s pretend that you have a list of people that you let the user select one or more people from. You want to pull back the person records of just the records they selected. Maybe you want to do a database round-trip to get the latest data before an edit or may you just want to filter the list in memory. In either case, this post addresses both of them since LINQ is used in both cases. The only difference may be is that instead of a List<People> you may be using an IQueryable<Person> if you are using the database. The tricky step is identical. I know because I was originally seeing the issue on the database example I had. I wrote this using just standard LINQ (to Objects) and in memory objects to make the running of this easy for you.

With that said, I have created a Person class below that is meant to simulate the Person table in the database. I then populate it with three records (Person objects). I then specify that I want just the people with the ids 1 or 2. I then run the query, dump the results. This shows the issue. We only get one record back. Then I run the next query and dump the results. This time the results are correct and give us two records as expected.

What is the difference in implementation you ask? There is very little different. In both cases there is a foreach loop and use the union method to build up a query. Union is similar to using an OR in the where clause of a SQL statement, but often the performance is much better. The downside is that much of the query is duplicated and makes maintenance a pain. No so with LINQ. It is very easy to do so here.

The ONLY difference between what works and what doesn’t work is that I declare a LOCAL variable INSIDE the foreach loop. I then use the LOCAL variable as a parameter in the LINQ expression. I have to assume this causes it to get the value from the variable before the LINQ expression is evaluated and thus it has the value we want. Otherwise, I assume it just looks at the current (last value) of the variable declared in the foreach statement itself at the end after the foreach loop has finished executing. I don’t know exactly how it works, but that is my assumption based on what I see from the behavior of the two methods. I don’t know if I would call this a bug or not, but it is not intuitive to me and makes for it to be very easy to write buggy code if not properly tested.

public class Person
{
    public string Name {get; set;}
    public int ID {get; set;}
}

List<Person> people = new List<Person>();

void Main()
{
    // simulate a database and a table called people that has 3 records
    people.Add(new Person{ Name="Brent", ID=1});
    people.Add(new Person{ Name="Lance", ID=2});
    people.Add(new Person{ Name="Kim", ID=3});
    
    // this could ids of the records the user selected
    List<int> idsToFind = new List<int>();
    idsToFind.Add(1);
    idsToFind.Add(2);
    
    // execute the query
    var allPeople = GetPeopleByIDsBroken(idsToFind);
    
    // output the results
    allPeople.Dump();
    
    // The results will be ONE record and that is Lance
    // This might be surprising because we passed in the ids for Lance and Brent.
    
    // execute the query
    var allPeople2 = GetPeopleByIDsWorks(idsToFind);
    
    // output the results
    allPeople2.Dump();
    
    // The results will be TWO record and that is Brent and Lance
    // This is the expected result and what we wanted.
}




IEnumerable<Person> GetPeopleByIDsBroken(List<int> ids)
{
    IEnumerable<Person> builtUpQuery = null;
    
    foreach (int id in ids)
    {
         // first time through only.
        if (builtUpQuery == null)
        {    
            builtUpQuery = people.Where(p => p.ID == id);
        }
            // all subsequent times
        else
        {
            builtUpQuery = builtUpQuery.Union(people.Where(p => p.ID == id));
        }
    }
    
    return builtUpQuery;
}

IEnumerable<Person> GetPeopleByIDsWorks(List<int> ids)
{
    IEnumerable<Person> builtUpQuery = null;
    
    foreach (int id in ids)
    {
        // We have to get a local copy of the id. 
        // Othewise, the value is not taken from the id in the foreach loop 
        // until the entire expression is evaluated
        int localCopyOfID = id;
        
         // first time through only.
        if (builtUpQuery == null)
        {    
            builtUpQuery = people.Where(p => p.ID == localCopyOfID);
        }
            // all subsequent times
        else
        {
            builtUpQuery = builtUpQuery.Union(people.Where(p => p.ID == localCopyOfID));
        }
    }
    
    return builtUpQuery;
}

Returning the row index using LINQ

Imagine you have a contest and each entry has a some points that they were awarded. To show an ordered list of them is easy enough. Something like the following will return the names and the related points.

Entries
.Select(entry => new {
entry.Name,
entry.TotalPoints,
}
)
.OrderBy (o => o.TotalPoints)

Now what if you wanted to show the rank (1st, 2nd, 3rd, etc) for each entry. So basically now we have three columns in the results: Rank, Name, TotalPoints. How would we do this?

The answer is hidden in the LINQ to Objects specially overloaded Select method that is available only when you use the Lambda syntax where if you specify a second parameter it will put the row index in it. Here is that same query, but with the Rank column added.

Entries.ToList()
.Select((entry, index) => new {
entry.Name,
entry.TotalPoints,
Rank = index + 1
}
)
.OrderBy (o => o.TotalPoints)

Notice how we used the ToList() to convert the query to a List first. This is critical as you will get a runtime error otherwise.

FYI, the error is: Unsupported overload used for query operator 'Select'.

Also note, the name index is not important. It is the order in the Select method that is important. The index is zero based, and I wanted to show 1 based Rankings so I added one to the index to get the Rank.

Using GroupBy with LINQ

I love LINQ, but sometimes the syntax can be a little strange. In particular, I find the group by syntax to be a little weird, but not too bad once I broke it down.

For this example, let’s assume we have a person table. The person table has a column called Score and has some other fields that don’t really matter for this example. The related table is a Gender table. It has one field of importance and that is the Name field. The two rows in this table are Male and Female. There is a foreign key in the Person table that points to the Gender table. Think of it this way. The person table is the main table, and there could be a drop down list to select the gender for the person.

With that in mind, we want to know what the total score for Males and Females. We want to do this using a group by. The results will be two columns: Gender and Score.

Here is an example of the output that we desire.

Gender	Score
Female	2013
Male	1923

Here is the lamdba based LINQ query we would need to do this.

   People
   .GroupBy (e => new {Gender = e.Gender.Name} )
   .Select (byGenderGroup =>
         new 
         {
            Gender = byGenderGroup.Key.Gender,
            Score = byGenderGroup.Sum (t => t.Score)
         }
   );

If we take this line by line we will see that this really isn’t so different from SQL that would be generated. Here is the SQL

   SELECT SUM([t0].[Score]) AS [Score], [t1].[Name] AS [Gender]
FROM [Person] AS [t0]
INNER JOIN [Gender] AS [t1] ON [t1].[ID] = [t0].[GenderID]
GROUP BY [t1].[Name]

Let’s go line by line of the LINQ code.

People is the main table we are working with

GroupBy creates an anonymous type (that way we can easily add additional columns to group by). In this case we group by the Gender.Name just like the last line of the SQL statement.

The Select lines create another anonymous type so that we can select just the columns we want to return. Notice that byGenderGroup doesn’t have a property called Gender. Since byGenderGroup doesn’t represent a person record and actually represents the grouped results, we can access any of the columns that we have grouped by in the above GroupBy line. In this case, Key collection only gives us one property, and that is Gender. The byGenderGroup does have many other methods that are available though. One example is the sum method. In general byGenderGroup has all the aggregate functions you would have in SQL.

FYI, you can also do this without using lamdba expressions, though I personally don’t like the syntax and find it confusing.

var results = from p in People group p by new {Gender = p.Gender.Name} into byGenderGroup
select new {byGenderGroup.Key.Gender, Score = byGenderGroup.Sum (t => t.Score)};

The choice is yours.

Performing queries using optional parameters without Dynamic SQL

Imagine you have a application that will be querying your database. The user is presented 3 different fields that will be used to filter the results that are shown to the user. They are all optional fields. The question is how can I best implement the solution.

There are several ways to approach the problem.

Option 1: Dynamic SQL

While this is an option, it is prone to SQL injection and can be difficult and error prone trying to defend against it. I know you can use parameters with it to some extent, but I think it is still not a good choice for the following reasons. It can also be difficult to read because of all the extra quotes, formatting, etc. Debugging is also difficult at best. For these reasons, try to stay away from this option.

Option 2: LINQ

You can use LINQ to SQL or LINQ to Entities to solve the problem. I highly recommend LINQ for this purpose if you have the opportunity.

Below is a snippet of code that queries the Person table by 3 optional parameters. Be sure to create and close your context object. This does not use a stored procedure, but LINQ is by design protected from SQL Injection. This is an extremely easy way to implement the desired functionality.

var people = from p in ctx.Person
            select p;
                           
if (nameParam != null)
{
    people = people.Where(p => p.Name == nameParam);
}

if (phoneParam != null)
{
    people = people.Where(p => p.Phone == phoneParam);
}

if (ssnParam != null)
{
    people = people.Where(p => p.SSN == ssnParam);
}

return people;

Option 3: Stored Procedure

Perhaps you don’t have LINQ or know LINQ yet. I recommend you take this as an opportunity and learn it, but that is a personal choice. If you can’t use LINQ or perhaps the project you are doesn’t use it and you want to keep the way of accessing the database consistent within the project, then LINQ may not be a good choice.

Within a stored procedure you could write if else statements to control what queries are executed. However, this can get messy for more than two optional parameters. This is because you need to have 4 cases and 4 select statements. If you have three optional parameters, then you have 8 cases and 8 select statements. If you have four optional parameter, then you have 16 cases and 16 select statements. As you can see this option is not a very scalable solution, and I don’t recommend it.

I do however recommend the following technique which uses OR and NULL checks in the where clause. Below is an example of a T-SQL snippet that does the same thing as the LINQ example.

select * from Person
where 
((@Name IS NULL) OR (Name = @Name))
AND
((@Phone IS NULL) OR (Phone = @Phone))
AND
((@SSN IS NULL) OR (SSN = @SSN))

Here is the same example, but with begins with search.

select * from Person
where 
((@Name IS NULL) OR (Name = @Name))
AND
((@Phone IS NULL) OR (Phone like @Phone + '%'))
AND
((@SSN IS NULL) OR (SSN like @SSN + '%'))

You can also use Coalesce or isnull. However, It becomes complex to try to do like instead of equal to. I didn’t actually complete this in my test due to the complexity and desire for simplicity of using the above example.

For information on performance of Coalesce, ISNULL, and NULL / OR combination, click here.

Using GridView, Entity Framework, LINQ, and an ObjectDataSource to implement a GridView that sorts and filters.

I went in search of an elegant and flexible way to implement a GridView that supports sorting (and the ability to add custom paging if I need it later, though I won’t cover that here) and uses a Data Access Layer (DAL). The filtering I am looking for is the ability to add any number of controls on the page and have them filter the results that are shown in the GridView. Based on the values of these user controls, I want to be able to do a begins with, or a range, or choose from a list of values, etc. I don’t want to be limited to just one value.

I am a believer that like SQL, you don’t want your LINQ queries all over the place. I believe a Data Access Layer (DAL) is a good place to put all your LINQ queries.

At the present time, this means that the ObjectDataSource is probably the best choice because it can call the DAL to do the query and not embed it in the EntityDataSource or LinqDataSource.

It is possible to get pretty good filtering and little to no code to do this using the Dynamic Data Future, but even then I using the DynamicFilter I found it difficult to modify the query to do things like ranges, or a begins with search for example. If you decide to go down that road, you might also want to check out the following post on how to do this in your own project. It makes searching based on a DropDownList or an AutoComplete field very easy. I wanted more flexibility than that. You can also get much of that same functionality from VS 2008 SP1 (yes the SP1 is required to get this functionality since SP1 is essentially a feature release, not a bug fix release).

The hard part of this is writing the DAL method, but is actually much easier than it used to be now that we have LINQ. In this case, I am using LINQ to Entities to query the Entity Framework.

Here is my DAL:

public class DAL
{
private MyEntities ctx = new MyEntities();

public IQueryable GetPerson(string firstName, string lastName, bool hasChildren, int? age, string sortExpr)
{
    // set a default sort order
    if (string.IsNullOrEmpty(sortExpr))
    {
        sortExpr = "FName";
    }

    var people = from p in ctx.Person
                    select new
                    {
                        ID = p.ID,
                        FName = p.FName,
                        LName = p.LName,
                        Age = p.Age,
                        NumChildren = p.NumChildren
                    };

    if (hasChildren)
        people = people.Where(p => p.NumChildren > 0);

    if (!string.IsNullOrEmpty(firstName))
        people = people.Where(p => p.FName.StartsWith(firstName));

    if (!string.IsNullOrEmpty(lastName))
        people = people.Where(p => p.LName.StartsWith(lastName));

    if (!age.HasValue)
        people = people.Where(p => p.Age > age);

    var sortedPeople = people.OrderBy(sortExpr)
        .Select("new(ID, FName, LName, Age, NumChildren)");

    return sortedPeople;
}
}

You may notice that the .OrderBy() method gives you a compiler error or is not in your Intellisense. You need to download it from Microsoft. Click here to download. In the zip file, look for the Dynamics.cs file. You can include it in your project or you can build the project that comes with and include the assembly it builds in your project. It is one file, so I like putting it in my project as source code.

This Dynamic class works very in scenarios like this because it actually supports the same syntax as the ObjectDataSource uses which is <ColumnName> <SortDirection>. If the sort direction is Ascending, then no direction is specifed by the ObjectDataSource. For example the syntax for sorting my FName in Ascending order, the sortExpression would be “FName” or “FName Descending” if you wanted to sort in Descending order.

You may also notice that I use Lambda expressions to do the additional where statements. Be sure to use the value returned by the Where() method since the Where() call doesn’t change (or even query the database). All the Where() does is adds another condition to the existing where clause in the generated sql. Each time Where() is called, the statement is ANDed to the existing where clause. The code is very optimized. I am quite impressed with the code generation.

For related details on sorting with the ObjectDataSource, check out my other post.

Below is the aspx code. The most important thing you get right is the SelectParameters/ControlParameters. The ControlID property needs to match the ID of the Controls you are using for Filtering. The Name property needs to match the parameter names in the DAL method you specified in the ObjectDataSource SelectMethod property.

First Name starts with: <asp:TextBox ID="FirstNameFilter" runat="server"></asp:TextBox><br />
Last Name Starts with: <asp:TextBox ID="LastNameFilter" runat="server"></asp:TextBox><br />
Has Children: <asp:CheckBox ID="cbHasChildren" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="Apply Filter" /> 

<asp:GridView ID="GridView1" runat="server" AllowSorting="True" 
AutoGenerateColumns="False" DataKeyNames="ID"  
DataSourceID="dsPeople"
    >
    <Columns>
        <asp:HyperLinkField DataNavigateUrlFormatString="PersonDetail.aspx?ID={0}" Text="View" DataNavigateUrlFields="ID" />
        
        <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" 
            SortExpression="ID" Visible="False"/>
        <asp:BoundField DataField="FName" HeaderText="First Name" 
            SortExpression="FName" />
        <asp:BoundField DataField="LName" HeaderText="Last Name" 
            SortExpression="LName" />
        <asp:BoundField DataField="NumChildren" HeaderText="Number of Children" 
            SortExpression="NumChildren" />
        <asp:BoundField DataField="Age" HeaderText="Age" 
            SortExpression="Age" />
    </Columns>
</asp:GridView>
    
<asp:ObjectDataSource ID="dsPeople" runat="server" 
    SelectMethod="GetPerson" 
    TypeName="MyNameSpace.DAL"
    SortParameterName="sortExpr"
    >
    <SelectParameters>
        <asp:ControlParameter ControlID="cbHasChildren" Name="hasChildren" Type="Boolean"/>
        <asp:ControlParameter ControlID="FirstNameFilter" Name="firstName" Type="String"/>
        <asp:ControlParameter ControlID="LastNameFilter" Name="lastName" Type="String"/>
    </SelectParameters>
    
</asp:ObjectDataSource>
    

The context type MyDataContext does not belong to any registered model.

Are you getting the following error message at runtime in your ASP.NET application that is using LINQ to SQL?

The context type MyDataContext does not belong to any registered model.

If you are, it is very likely you have not registered you DataContext. To register you DataConext open your Global.asax.cs and add the following to the RegisterRoutes() method.

model.RegisterContext(typeof(MyDataContext), new ContextConfiguration() { ScaffoldAllTables = true });

The GridView 'GridView1' fired event Sorting which wasn't handled.

If you get the error:

The GridView 'GridView1' fired event Sorting which wasn't handled.

You are likely using an ObjectDataSource and then set AllowSorting to true or you are binding to directly your GridView in Page_Load using something like this.

It means you using a GridView that has AllowSorting=”true” equal to true and for some reason nothing has told it what will handle the sorting. The easiest way to avoid this is to use a DataSource control such as an EntityDataSource, SqlDataSource, or LinqDataSource control. The ObjectDataSource will not help you out of the box though. Some extra stuff is required. I’ll show you that later.

This page entry is broken up into to sections. One if you are binding directly to the GridView in your page load and thus have no DataSource assigned to the GridView. Another if you have are using an ObjectDataSource.

In both sections I assume you are using LINQ to access the database, but you could use anything to talk to the database. The logic that needs to be implemented is still the same. I also assume you have an object that encapsulates your database access (a Database Access Layer (DAL)).

For this example, let’s assume you have used the ADO.NET Entity Data Model in Visual Studio to create your entities. In this example we have one entity called Person. It has 3 properties: ID, FName, LName.

Data Access Layer

Below is a solution if you are using an LINQ to Entities, though it would be virtually identical to LINQ to SQL. A similar solution could be used for SQL, though in that case you would translate the requests to SQL statements.

public class DAL
{
    private MyEntities ctx = new MyEntities();

    public IQueryable GetPerson(string sortExpression)
    {
        // set a default sort order for when the page is first rendered
        if (string.IsNullOrEmpty(sortExpression))
        {
            sortExpression = "FName Descending";
        }

        var people = from p in ctx.Person
                        select p;

        var sortedPeople = people.OrderBy(sortExpression)
            .Select("new(ID, FName, LName)");
        
        return sortedPeople;
    }}

You may notice that the .OrderBy() method gives you a compiler error or is not in your Intellisense. You need to download it from Microsoft. Click here to download. In the zip file, look for the Dynamics.cs file. You can include it in your project or you can build the project that comes with and include the assembly it builds in your project. It is one file, so I like putting it in my project as source code.
This Dynamic class works very in scenarios like this because it actually supports the same syntax as the ObjectDataSource uses which is <ColumnName> <SortDirection>. If the sort direction is Ascending, then no direction is specifed by the ObjectDataSource. For example the syntax for sorting my FName in Ascending order, the sortExpression would be “FName” or “FName Descending” if you wanted to sort in Descending order.
The GridView sortingEvent also uses very similar syntax. In either case, this saves us from writing a bunch of if-else or switches for each column and sort direction. The choice is yours. This is just so easy, and it is clean.

Binding ObjectDataSource to GridView

This is by far easier of the two methods. I highly recommend using a DataSource such as the ObjectDataSource. The code is much simpler.
To make the ObjectDataSource sort all you have to do is set the DataSourceID property on the GridView to the ID of your ObjectDataSource.
You do have to tell the ObjectDataSource some things about your Data Access Layer though. You need to tell it the type for your Data Access Layer, the method to call, and what the parameter name is for sortExpression the GridView will pass you.
Here is my ObjectDataSource that I defined for the Data Access Layer we defined above.

<asp:ObjectDataSource ID="dsContracts" runat="server" 
    SelectMethod="GetPerson" 
    TypeName="DataModel.DAL"
    SortParameterName="sortExpression"
    >
</asp:ObjectDataSource>

Binding Directly to GridView in Page Load

If you want to work a little harder you can implement the logic using the GridView and no ObjectDataSource. If you are bind directly to your GridView in your page load, all you to do to stop this error message is handle the Sorting event on your GridView. While this stops the error message, it doesn’t give you sorting when a column header is clicked. You need to put some logic in the Sorting event for it to do something useful.

You are likely binding your DAL to your GridView using something like this or maybe conditionally if it isn’t a postback:

protected void Page_Load(object sender, EventArgs e)
{
    GridView1.DataSource = new DAL().GetPerson("");
    GridView1.DataBind();
}

The GridView does NOT set the SortDirection property in this event handler unless an DataSource object is set. This means that Sorting event ALWAYS will have a e.SortDirection equal to SortingDirection.Ascending. This is a bug in my mind, but I think Microsoft just says it is by design (or bad design if you ask me). For more explanation on this please see here for the response from Microsoft.

As a recommended workaround, we need to track the SortDirection ourselves. In order to do something useful, we need to also track the column that was clicked so that we know when to reset the sort direction to the default direction.

Here is the code to handle the sorting event for GridView. Be sure to wire it up to your GridView.

protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{

    // get values from viewstate
    String sortExpression = ViewState["_GridView1LastSortExpression_"] as string;
    String sortDirection = ViewState["_GridView1LastSortDirection_"] as string;

    // on first time header clicked ( including different header), sort ASCENDING
    if (e.SortExpression != sortExpression)
    {
        sortExpression = e.SortExpression;
        sortDirection = "Ascending";
    }
        // on second time header clicked, toggle sort
    else
    {
        if (sortDirection == "Ascending")
        {
            sortExpression = e.SortExpression;
            sortDirection = "Descending";
        }
            // Descending
        else
        {
            sortExpression = e.SortExpression;
            sortDirection = "Ascending";
        }
    }

    // save state for next time
    ViewState["_GridView1LastSortDirection_"] = sortDirection;
    ViewState["_GridView1LastSortExpression_"] = sortExpression;

    // NOTE: Depending on the syntax you require for your sortExpression parameter
    // to your method, you may need to convert the sort expression to that syntax.
    GridView1.DataSource = new DAL().GetPerson(sortExpression + " " + sortDirection);
    GridView1.DataBind();
}

Getting the SQL that was generated using LINQ to Entities

LINQ to Entities doesn’t have debugger support for getting the SQL that was generated for a query like LINQ to SQL does. However, you can easily get the generated SQL with a few lines of code. I have added some additional lines of code to make it more robust. I recommend adding the following method to your partial class that inherits from ObjectContext. This designer class is created by default when you create an ADO.NET Entity Data Model, but you don’t want to edit that. Instead you want to add a partial class with the same name and in the same namespace.  If you don’t want to put the code there you could modify it slightly to take the ObjectContext as a parameter and put it anywhere you want.

/// <summary>
/// For debugging only. Returns the SQL statement that is generated
/// by LINQ for an IQueryable object. This does NOT execute the query
/// </summary>
/// <param name="query">The IQueryable object</param>
/// <returns>The generated SQL as a string</returns>
public string GetGeneratedSQL(IQueryable query)
{
    string sql = string.Empty;
    bool weOpenedConnection = false;
    try
    {
        if (Connection.State != ConnectionState.Open)
        {
            Connection.Open();
            weOpenedConnection = true;
        }
        sql = (query as ObjectQuery).ToTraceString();
    }
    finally
    {
        if (weOpenedConnection)
        {
            Connection.Close();
        }
    }
    return sql;
}

Here is how you use it.

using (ICA3Entities ctx = new ICA3Entities())
{
    var query = from t in ctx.MyTable
                select t;
    string sql = ctx.GetGeneratedSQL(query);
}

For tools and more information on other options you may want to check out: http://www.scip.be/index.php?Page=ArticlesNET13#ToTraceString

Downloading Binary content using ASP.NET and LINQ

Here’s the scenario. You have some binary data that is in a database. You want to use LINQ to SQL or LINQ to Entities to pull the data out of the database and return the user for viewing when they hit a particular url.

Let’s pretend you have a page called Download.aspx and you want to use the primary key in the url to specify what file should be downloaded when the user hits this page.

For example, if the url is http://myapp/Download.aspx?AttachmentID=123 where 123 is the primary key in a table called Attachment that has our binary data.

Below is an LINQ to Entities example, but a LINQ to SQL would be virtually identical.

protected void Page_Load(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(Request.QueryString["AttachmentID"])) 
    {
        int attachmentID = Convert.ToInt32(Request.QueryString["AttachmentID"]);

        using (MyEntities ctx = new MyEntities())
        {
            var attachment = ctx.Attachment.FirstOrDefault<MyAttachment>(a => a.ID == attachmentID);
            if (attachment != null)
            {

                byte[] binaryData = attachment.DataField;
                Response.Clear();
                Response.AddHeader("content-disposition", string.Format("attachement; filename=\"{0}\"", attachment.FileName));
                Response.ContentType = attachment.MimeType;
                Response.BinaryWrite(binaryData);
                Response.End();

            }

        }
    }
}

LINQ – A Technology Overview

What is LINQ?

LINQ is short for Language Integrated Query. It is a Microsoft technology that allows programmers to query just about anything using SQL like syntax.

LINQ can be extended to support just about anything. For example, you could write something to allow you to query Flickr using LINQ. Microsoft has done an excellent job of making virtually all objects in C# 3.0 (or VB 9) queryable using LINQ. This means you can query lists, collection, objects, etc.

Why use LINQ?

Performance

Performance is extremely good with LINQ. The reason is that queries are optimized to only bring back the data you need. This is key for roundtrips to databases. In-memory queries are equally as optimized.

Strongly-Typed data

Strongly-typed data makes programming much easier and less error-prone. This also means that Intellisense works for all your objects. This gives the compiler a chance to help find issues with code changes.

No SQL Injection

LINQ provides protection from SQL Injection just a stored procedures in databases do. This is one of the major reasons I used stored procedures, and now I don’t have to.

Leverage Skills

Imagine being able to query anything using the same syntax and the same skill. This means you learn it once and use it everywhere. This is extremely useful and efficient. It also means that programmers can actually get away with not being well versed in SQL. Though, I think SQL and database knowledge is still very important. :) In terms of having to learn new API’s for services such as Flickr, Facebook, Amazon, etc, the learning curve is greatly reduced when using LINQ.

Future Standard

The general hope is that in 5 to 10 years all programming languages will have support to do queries as concept that is built into the language just like a for loop. Java appears to be moving in this direction with JPA (Java Persistance API) also. So, you might consider getting used to the idea and sharpening your skills early.

Less Code

When dealing with a database, you can have your entities that map to tables in your database automatically created for you. The mapping is editing using a visual designer in Visual Studio 2005 or later. The entity code is hidden from you, but you use partial classes to change the classes to suit your needs. This means much less code to maintain in your Data Access Layer. I still recommend a Data Access Layer so that you are not duplicating LINQ code and you are not tightly coupled to LINQ, but that is technically up to you.

Stored Procedure Support

Stored Procedures can still be used. This means if you prefer to use stored procedures you can. This is important in environments where databases are locked down and only allow access via stored procedures. The advantage is that you still don’t have to create or do much to maintain your entities.

List of LINQ Providers - What does LINQ work with?

The list is growing every day, but here is a good snapshot of what LINQ currently works with. Well, at least what I thought was pretty mainstream and useful. :) Each extension of LINQ has the naming conventions like LINQ to <thing> or LINQ over <thing> or <thing>LINQ. Most except the first few are created by third parties.

If you want to try to write your own, you might try this toolkit, though I have not tried it. Keep in mind, many times you don’t need a full blown provider. LINQ to Objects makes it possible to query most anything that is some sort of collection, list, etc.

• LINQ to Objects – works with IEnumerable, List, Array, Dictionary, etc.
• LINQ to SQL – Visual Studio supports this very well, and is what most people think of when they here LINQ. This allows you to use a Visual Designer to create and modify your entities.
• LINQ to Entities – Entities is short for the ADO.NET Entity Framework (EF). EF is essentially an Object/Relationship Mapper that Visual Studio 2008 SP1 handles quite well.
• LINQ to XML – Makes working with XML files much easier.
• LINQ to DataSet – Provides full query capabilities to your DataSets.
• DbLinq - LINQ to Oracle, MySQL, PostgreSQL, SQLite
• LINQ to CSV
• LINQ to MAPI
• LINQ to LLBLGen
• LINQ to NHibernate
• LINQ to Google
• LINQ to System Search – Windows Search
• LINQ to Amazon
• LINQ to LDAP
• LINQ to Flickr
• LINQ to SharePoint
• LINQ to Active Directory
• LINQ to Excel
• LINQ to JSON
• LINQ to JavaScript
• LINQ to WMI
• LINQ for PHP
• LINQ to FQL (Facebook)

If you want to see an example of how to actually use LINQ, I recommend my entry titled: LINQ – A brief overview