Archive

Posts Tagged ‘Linq2Sql’

Entity Framework Sorting by master then detail

As I get more and more comfortable with Entity Framework, I realize that there are some simple things within Linq2Sql that I absolutely miss. One of these features is the ability to Sort by a property in the underlying collection you are returning then by a property on a child collection as well.

This is easy with Linq2Sql, all I have to do is use the DataLoadOptions and embed a lambda expression that contains my order by expression. Once that is done, I can then pass the options the underlying LoadOptions property of the instance that I am working with and it is ready to go. This will provide me parent/child sorting capabilities.

There is another way that you can sort fairly easily in both Entity Framework and Linq2Sql but I personally don’t like it since most of my development uses Silverlight and WCF RIA Services. I like to get change tracking for free. This other way is to use projection when basically allows you to create a new anonymous type and return this new type with all of the sorting and flattening out if you need it. This approach is good for read-only screens or when you know that you need to denormalize your data across several entities and you are not concerned about losing the change tracking and have a mechanism for handling that yourself.

Now on to the way to get Parent/Child or Master/Detail sorting in the Entity Framework. Here is a sample Query that exposes a Customer object with its corresponding Order collection attached as well. The Customer object is also sorted by the CustomerName:

[Query()]
public IQueryable<Customer> ReturnCustomers()
{
    return this.ObjectContext.Customer
    .Include("Order")
    .OrderBy(x => x.CustomerName);

}

This satisfies the top level ordering but since the Order entity is a collection off of the Customer entity, I have no way to embed a Linq expression to force sorting the child record as well. I have tried using several different approaches but ultimately I believe this is a limitation with WCF RIA Services more than it is with Entity Framework.

I will now show you how I am facilitating the child detail level sorting on the client side of the application. First I am going to expose this newly created Query via a DomainDataSource. The following is a snippet of XAML showing all of the pieces working together:

<UserControl.Resources>
  <CollectionViewSource x:Name="cvsOrders" 
    Source="{Binding CurrentCustomer.Order}">
    <CollectionViewSource.SortDescriptions>
      <compMod:SortDescription PropertyName="OrderDate" 
        Direction="Ascending"/>
    </CollectionViewSource.SortDescriptions>
  </CollectionViewSource>
</UserControl.Resources>

<riaCtls:DomainDataSource x:Name="_ddsCustomers" 
    QueryName="ReturnCustomersQuery"
    DomainContext="{Binding DomainContext, Mode=TwoWay}">
</riaCtls:DomainDataSource>
<ListBox x:Name="lboCustomers"
    Grid.Column="0"
    ItemsSource="{Binding ElementName=_ddsCustomers, Path=Data}"
    SelectedItem="{Binding CurrentCustomer, Mode=TwoWay}">
</ListBox>
<toolkit:DataGrid x:Name="dgOrders"
    Grid.Column="1"
    ItemsSource="{Binding Source={StaticResource cvsOrders}}">
</toolkit:DataGrid>

Let’s start with the DomainDataSource first. As you can see, it is simply calling the Query that was created on the server. Next we see that we have a lbCustomers ListBox that sets the Data property of the DomainDataSource to its ItemsSource property. I am also setting the SelectedItem of the ListBox to the property exposed on my ViewModel called, “CurrentCustomer”. This allows me to access from other controls.

Note: The namespace alias compMod comes from the following mapping: xmlns:compMod=”clr-namespace:System.ComponentModel;assembly=System.Windows”

Finally, I have created a CollectionViewSource object. This may seem old hat to most WPF programmers and is a great addition to Silverlight as well. I set the Source property of the CollectionViewSource to the property I exposed on the ViewModel but I also reference the child collection of Order. I then add a SortDescription that sorts the Order collection based on the OrderDate property.

Now when this screen opens, it will be ordered by CustomerName first, then when a user selects a given Customer, all of the Orders will also be ordered by OrderDate as well.

This may seem to be a lot of work getting the Master/Detail or Parent/Child ordering to work but this solution turns out to be quite flexible and allows you to extend it to pretty much whatever you can think of.

Hope this helps….

Dynamically changing the connection string for WCF RIA Services Linq2Sql

May 20, 2010 1 comment

In my last posts, I demonstrated how to provide a dynamic connection string for WCF RIA Services Linq2Entities. I will now show how this is done with Linq2Sql.

Okay, so you want to provide the ability to change the underlying connection string for a given WCF RIA Services request? The following is an example of creating a WCF RIA Services Class Library. I have also added a Linq2Sql classes model as well a DomainService Class.

WCF RIA Services - Linq2Sql

One thing that you will notice, is that I also manually created the DashboardDomainService.partial.vb file. It is in this class that I implement the ability to switch connection strings. I have found that since the other files are code generated, that by having my logic in a partial class, I can bypass the headache when I need to update or change my model and domain service.

The main class derives from LinqToSqlDomainService where T is the model we are building our DomainService. In this case it would be DashboardDataContext.

Let’s take a look at partial class now:

public partial class DashboardDomainService
{
    /// <summary>
    /// We need override this function since we are dynamically changing the
    /// connection string based on the user's session information.
    /// </summary>
    /// <returns></returns>
    protected override DashboardDataContext CreateDataContext()
    {
        DashboardDataContext ctx = null;
        if (!string.IsNullOrEmpty(ServiceContext.User.Identity.Name))
        {
            SecureLoginEntities sctx = new SecureLoginEntities();
            var userSession = (from c in sctx.sl_us_UserSession where
                c.sl_us_Token.HasValue.Equals(true) &&
                c.sl_us_Token.Value.Equals(new G
                    uid(ServiceContext.User.Identity.Name)) select
                c).FirstOrDefault();
            if (userSession != null)
            {
                sl_d_Database db = (from c in sctx.sl_d_Database where
                    c.sl_d_DatabaseIdent == userSession.sl_us_DatabaseId
                    select c).FirstOrDefault();
                if (db != null)
                {
                    // Initialize the connection string builder for the
                    // underlying provider.
                    SqlConnectionStringBuilder sqlBuilder =
                         new SqlConnectionStringBuilder();

                    // Set the properties for the data source.
                    sqlBuilder.DataSource = serverName;
                    sqlBuilder.InitialCatalog = databaseName;
                    sqlBuilder.IntegratedSecurity = false;
                    sqlBuilder.UserID = userName;
                    sqlBuilder.Password = password;

                    string connString = sqlBuilder.ToString();
                    ctx = new DashboardDataContext(connString);
                }
            }
        }

        return ctx;
    }

};

Basically what I want to happen is that whenever there is a request to this service, I want to override the CreateContext method and change the connection string accordingly. I have some authentication logic that I am using that helps me determine what database the user wants to use but once I get past that I basically create a new instance of my context passing in the newly created connection string.

For this example, I am building connection strings for SQL Server. I am using the SqlConnectionStringBuilder class. I then just call the ToString method to get the newly created connection string.

When all is said and done, you will get a connection string that looks like the following example taken from my Web.config:

<configuration>
  <connectionStrings>

    <add name="Common.Dashboard.Data.Web.My.MySettings.emgov_dataConnectionString"
        connectionString="Data Source=BUNKERHILL;
        Initial Catalog=emgov_data;User Id=emgovuser;Password=xxxx;"
        providerName="System.Data.SqlClient" />

  </connectionStrings>
  ...
<configuration>

(NOTE: I put break carriage return in this so that you could read it better but it would not be like this in your Web.config file.)

By following this example, you can now provide the ability to switch between connection string dynamically. You can even go as far as storing the connection information in a database and build the connection string on the fly as well.

Hope this helps…

Dynamically changing the connection string for WCF RIA Services Linq2Entities

May 20, 2010 39 comments

One of the business requirements I get a lot is the ability to switch which database they use when they authenticate. I have just recently ported my applications over from Linq2Sql to Linq2Entties and I have noticed just enough difference that I figured I would blog about how to do this. I will also publish a subsequent post that shows how this is accomplished in Linq2Sql.

Okay, so you want to provide the ability to change the underlying connection string for a given WCF RIA Services request? The following is an example of creating a WCF RIA Services Class Library. I have also added an ADO.NET Entity Data Model as well a DomainService Class.

Data Access Project

One thing that you will notice, is that I also manually created the DataAccessDomainService.partial.cs. It is in this class that I implement the ability to switch connection strings. I have found that since the other files are code generated, that by having my logic in a partial class, I can bypass the headache when I need to update or change my model and domain service.

Note that I had to add the partial keyword to the main class in order to get my partial class to work. Also the main class derives from LinqToEntitiesDomainService where T is the model we are building our DomainService. In this case it would be DataAccessContext.

Let’s take a look at partial class now:

public partial class DataAccessDomainService
{
    /// <summary>
    /// We need override this function since we are dynamically changing the
    /// connection string based on the user's session information.
    /// </summary>
    /// <returns></returns>
    protected override DataAccessContext CreateObjectContext()
    {
        DataAccessContext ctx = null;
        if (!string.IsNullOrEmpty(ServiceContext.User.Identity.Name))
        {
            SecureLoginEntities sctx = new SecureLoginEntities();
            var userSession = (from c in sctx.sl_us_UserSession where
                c.sl_us_Token.HasValue.Equals(true) &&
                c.sl_us_Token.Value.Equals(new G
                    uid(ServiceContext.User.Identity.Name)) select
                c).FirstOrDefault();
            if (userSession != null)
            {
                sl_d_Database db = (from c in sctx.sl_d_Database where
                    c.sl_d_DatabaseIdent == userSession.sl_us_DatabaseId
                    select c).FirstOrDefault();
                if (db != null)
                {
                    string connString = EntityConnectionStringHelper.Build(
                        db.sl_d_ServerName,
                        db.sl_d_DatabaseName,
                        db.sl_d_UserName,
                        db.sl_d_Password,
                        "DataAccess");
                    ctx = new DataAccessContext(connString);
                }
            }
        }

        return ctx;
    }

};

Basically what I want to happen is that whenever there is a request to this service, I want to override the CreateContext method and change the connection string accordingly. I have some authentication logic that I am using that helps me determine what database the user wants to use but once I get past that I basically create a new instance of my context passing in the newly created connection string.

Note: The string “DataAccess” that I am passing into the EntityConnectionStringHelper.Build method above is actually from the Web.Config connection string. It represents the *.csdl, *.ssdl, *.msl names that are represented in the style that the Entity Framework requires.

In order to help me and get some code reuse, I created a helper class that helps me with buildng my connection string. This is one of the main differences between Linq2Sql and Linq2Entities.

Let’s look at this code.

public class EntityConnectionStringHelper
{
    public static string Build(
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Specify the provider name, server and database.
        string providerName = "System.Data.SqlClient";
        return EntityConnectionStringHelper.Build(
            providerName,
            serverName,
            databaseName,
            userName,
            password,
            metaData);
    }
    public static string Build(
        string providerName,
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Initialize the connection string builder for the
        // underlying provider.
        SqlConnectionStringBuilder sqlBuilder =
            new SqlConnectionStringBuilder();

        // Set the properties for the data source.
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.IntegratedSecurity = false;
        sqlBuilder.UserID = userName;
        sqlBuilder.Password = password;

        // Build the SqlConnection connection string.
        string providerString = sqlBuilder.ToString();

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        //Set the provider name.
        entityBuilder.Provider = providerName;

        // Set the provider-specific connection string.
        entityBuilder.ProviderConnectionString = providerString;

        // Set the Metadata location.
        entityBuilder.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl",
            metaData);

        return entityBuilder.ToString();
    }
};

Basically this class has two static methods called Build. One calls into the other passing in the providername. In order to correctly build a Linq2Entities connection string you have to also include the Metadata. This is the main difference between Linq2Sql and Linq2Entities. For this example, I am building connection strings for SQL Server. I am using the SqlConnectionStringBuilder class in conjunction with the EntityConnectionStringBuilder class.

When all is said and done, you will get a connection string that looks like the following example taken from my Web.config:

<configuration>
  <connectionStrings>

    <add name="DataAccessContext"
      connectionString="metadata=res://*/DataAccess.csdl|res://*/DataAccess.ssdl|
        res://*/DataAccess.msl;provider=System.Data.SqlClient;
        provider connection string=&quot;Data Source=bunkerhill;
        Initial Catalog=emgov_data;Integrated Security=False;
        User Id=emgovuser;Password=xxxx;
        MultipleActiveResultSets=True&quot;"
        providerName="System.Data.EntityClient" />

  </connectionStrings>
  ...
<configuration>

(NOTE: I put break carriage return in this so that you could read it better but it would not be like this in your Web.config file.)

By following this example, you can now provide the ability to switch between connection string dynamically. You can even go as far as storing the connection information in a database and build the connection string on the fly as well.

Hope this helps…

Switching from Linq2Sql to Entity Framework

There are just enough differences between Linq2Sql and Entity Framework that it can be very irritating. I have been migrating some source code from Silverlight 4 RC to the RTM and I decided that since Linq2Sql was no longer being considered a first class citizen, I would go ahead and migrate the data access layer as well. Easy right? Well, not exactly a walk in the park. I have been using WCF RIA Services as my transport layer for my Silverlight applications and it has worked out really well.

However, there are a few things that I liked better in Linq2Sql than in Entity Framework:

1. Loading associations
I think that the Linq2Sql team nailed it when they gave you the DataLoadOptions class with the LoadWith method.
You can see the difference yourself by looking at the following code snippet:

[Query()]
public IQueryable<Customer> ReturnCustomersIncludingProducts()
{
    DataLoadOptions loadOpts = new DataLoadOptions();
    loadOpts.LoadWith<Customer>((o) => o.Products);
    this.DataContext.LoadOptions = loadOpts;
    return this.DataContext.Customers;
}

Now look at the same example in Entity Framework:

[Query()]
public IQueryable<cg_f_Form> ReturnCustomersIncludingProducts()
{
    return this.ObjectContext.Customers
        .Include("Products")
        .OrderBy(c => c.CustomerName);
}

What I don’t like about the Entity Framework implementation is that it uses magic strings and there is no compile time checking. At least with the Linq2Sql version you could get an error if the underlying model changed.

2. DomainDataSource
Another issue that I have with the Entity Framework is the added work that you need to do in order for paging to work correctly. In Linq2Sql you just add your paging criteria and then went on your happy way. The Xaml side code is provided below:

<riaCtls:DomainDataSource 
    x:Name="ddsMasterForms" QueryName="ReturnCustomersIncludingProductsQuery" AutoLoad="False" LoadSize="50" PageSize="25"
    DomainContext="{Binding DomainContext, Mode=TwoWay}" />

In Entity Framework if you do nothing else and let your application run, you will get the following exception:

“Load operation failed for query ‘ReturnCustomersIncludingProductsQuery’. The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.”

If you look back at both version of the server Queries, you notice that you will have to add an OrderBy statement. It turns out that this is required because the DomainDataSource’s Load operation tries use the ‘Skip’ and this method requires that the underlying data source be sorted. This was never a requirement in Linq2Sql.

So there you have it, if you are transitioning from Linq2Sql to Entity Framework, you will find a few interesting issues. Hopes this helps….