Entity Framework POCO and JSON serialization

An application I've been working utilizes the jQuery DataTables plugin and server side processing for pagination.

My initial set up had my controller call a view model which in turn called my business layer to search for some entities. The view model method returned an object (DataTableModel) which encapsulates all of the properties needed for the DataTable plugin including a list of objects/entities. So far so good.

Next step has the controller serializing the DataTableModel to JSON so the DataTable plugin could do it's thing with it. Unfortunately this is where things fell down. Since the search results contained within the DataTableModel are a list of Entity Framework generated POCO objects with all of it's navigation properties the JSON serializer would try to serialized the entire object. Not surprising I suppose, however, I kind of expected it would figure out that if the navigation properties were null it would ignore them and not try to actually load them....but alas no.

Sooooo, my next adventure was figuring out how to attack this problem. The best practice it seems would be to have a view model for anything and everything you'd want to return to the UI whether directly to a view orvia Ajax. I don't necessarily have a problem with this, but at the same time I do not think it unrealistic to want to return POCO objects to the UI either.

Initially I decided to try and return a list of ExpandObjects for the JSON serializer to work with. This would serialize without error, however, the result is not quite what I'd hoped for. The result was the serializer creating key/value pairs for each property in the object vs. actually having an object property with respective value.

In the end I decided to have my Linq query return a dynamic with all of the information needed for display in my table. I then updated my DataTableModel to have a List<dynamic> vs. List<EntityFrameworkPOCO>. This allows the JSON serializer to work without fail.

Determine all foreign keys in a database

Quick script to determine all foreign keys in your SQL Server database:

SELECT 
    f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,
    fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id) AS ReferenceColumnName
FROM 
    sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

WCF services and non .NET clients

We recently began exposing some of our WCF services to clients. Previously all of our services were consumed internally by our own applications which of course were all .NET based.

Upon one of our clients attempting to consume the WSDL for one of our services they received XML serialization errors. Further investigation reveals that the default form of the WSDL may contain XSD includes. This form of the WSDL does not play well with clients other than .NET.

So, the end result was flattening the WSDL by implementing a custom behavior (see here: WCF Extras )

Security settings for this service require 'Basic' Authentication

Had an issue calling WCF service today. The following exception was being thrown when service was called:

WebHost failed to process a request.

Sender Information: System.ServiceModel.ServiceHostingEnvironment+HostingManager/35320229

Exception: System.ServiceModel.ServiceActivationException: The service '/InteliChartVendorCommunication/VendorService.svc' cannot be activated due to an exception during compilation.  The exception message is: Security settings for this service require 'Basic' Authentication but it is not enabled for the IIS application that hosts this service..

Ensured Basic authentication was indeed enabled in IIS before getting stumped on what actual issue could be. Turns out it was CustomErrors setting. Value was set to "off" vs "Off". Would have expected different exception from .NET (i.e. web.config parse exception) but it works now either way.

Deleting a row from self-referencing table

Came across this the other day and thought “this would be a great interview question!”

I’d created a table with a self-referencing foreign key. The application was calling a stored procedure I’d created to delete a row which caused but of course…a foreign key exception.

You may say “why not just use a the cascade delete option?” Good question, easy answer. With a typical foreign key relationship between different tables which would work. However, even SQL Server cannot do a cascade delete of a row on a table with self-referencing foreign key.

So, what do you do?……

In my case I re-wrote the stored procedure to take advantage of recursion:

 

-- recursively deletes a Foo
ALTER PROCEDURE [dbo].[usp_DeleteFoo]
     @ID int
    ,@Debug bit = 0   
AS
    SET NOCOUNT ON;

    BEGIN TRANSACTION
    BEGIN TRY
        DECLARE @ChildFoos TABLE
        (
            ID int
        )
       
        DECLARE @ChildFooID int       
       
        INSERT INTO @ChildFoos
        SELECT ID FROM Foo WHERE ParentFooID = @ID
       
        WHILE EXISTS (SELECT ID FROM @ChildFoos)
        BEGIN
            SELECT TOP 1
                @ChildFooID = ID
            FROM
                @ChildFoos
               
            DELETE FROM @ChildFoos WHERE ID = @ChildFooID
           
            EXEC usp_DeleteFoo @ChildFooID
        END   
       
               
        DELETE FROM dbo.[Foo]
        WHERE [ID] = @ID
       
        IF @Debug = 1 PRINT 'DEBUG:usp_DeleteFoo, deleted - ID: ' + CONVERT(VARCHAR, @ID)
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        DECLARE @ErrorMessage VARCHAR(4000), @ErrorSeverity INT, @ErrorState INT
        SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()

        IF @ErrorState <= 0 SET @ErrorState = 1

        INSERT INTO ErrorLog(ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage)
        VALUES(ERROR_NUMBER(), @ErrorSeverity, @ErrorState, ERROR_PROCEDURE(), ERROR_LINE(), @ErrorMessage)

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
    END CATCH

 

This procedure will first determine any rows which have the row we wish to delete as it’s parent. It then simply iterates each child row calling the procedure recursively in order to delete all ancestors before eventually deleting the row we wish to delete.

Auto-hydrate your objects with ADO.NET

Recently while writing the monotonous code for pulling data out of a DataReader to hydrate some objects in an application I suddenly wondered "is this really necessary?"

You've probably asked yourself the same question, and many of you have:
- Used a code generator
- Used a ORM such as Entity Framework
- Wrote the code anyway because you like busy work    


In most of the cases I've dealt with when making a call to a stored procedure the column names match up with the properties of the object I am hydrating. Sure that isn't always the case, but most of the time it's 1 to 1 mapping. 

Given that fact I whipped up the following method of hydrating my objects without having write all of the code. First I'll show the code, and then explain what it is doing. 

    /// <summary>
    /// Abstract base class for all Shared objects.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    [Serializable, DataContract(Name = "{0}SharedBase")]
    public abstract class SharedBase<T> where T : SharedBase<T>
    {
        private static List<PropertyInfo> cachedProperties;

        /// <summary>
        /// Hydrates derived class with values from record.
        /// </summary>
        /// <param name="dataRecord"></param>
        /// <param name="instance"></param>
        public static void Hydrate(IDataRecord dataRecord, T instance)
        {
            var instanceType = instance.GetType();
           
            //Caching properties to avoid repeated calls to GetProperties.
            //Noticable performance gains when processing same types repeatedly.
            if (cachedProperties == null)
            {
                cachedProperties = instanceType.GetProperties().ToList();
            }
           
            foreach (var property in cachedProperties)
            {
                if (!dataRecord.ColumnExists(property.Name)) continue;

                var ordinal = dataRecord.GetOrdinal(property.Name);

                var isNullable = property.PropertyType.IsGenericType &&
                                 property.PropertyType.GetGenericTypeDefinition() == typeof (Nullable<>);
                var isNull = dataRecord.IsDBNull(ordinal);

                var propertyType = property.PropertyType;

                if (isNullable)
                {
                    if (!string.IsNullOrEmpty(propertyType.FullName))
                    {
                        var nullableType = Type.GetType(propertyType.FullName);
                        propertyType = nullableType != null ? nullableType.GetGenericArguments()[0] : propertyType;
                    }
                }

                switch (Type.GetTypeCode(propertyType))
                {
                    case TypeCode.Int32:
                        property.SetValue(instance,
                                          (isNullable && isNull) ? (int?) null : dataRecord.GetInt32(ordinal), null);
                        break;
                    case TypeCode.Double:
                        property.SetValue(instance,
                                          (isNullable && isNull) ? (double?) null : dataRecord.GetDouble(ordinal),
                                          null);
                        break;
                    case TypeCode.Boolean:
                        property.SetValue(instance,
                                          (isNullable && isNull) ? (bool?) null : dataRecord.GetBoolean(ordinal),
                                          null);
                        break;
                    case TypeCode.String:
                        property.SetValue(instance, (isNullable && isNull) ? null : isNull ? null : dataRecord.GetString(ordinal),
                                          null);
                        break;
                    case TypeCode.Int16:
                        property.SetValue(instance,
                                          (isNullable && isNull) ? (int?) null : dataRecord.GetInt16(ordinal), null);
                        break;
                    case TypeCode.DateTime:
                        property.SetValue(instance,
                                          (isNullable && isNull)
                                              ? (DateTime?) null
                                              : dataRecord.GetDateTime(ordinal), null);
                        break;
                }
            }
        }
    }

 

Here is a class which utilizes the above:

[Serializable]
[DataContract]
public class foo : SharedBase<foo>
{
    [DataMember]
    public int? ID { get; set; }

    [DataMember]
    public string Name { get; set; }

    [DataMember]
    public string Description { get; set; }

    [DataMember]
    public string Subject { get; set; }

    [DataMember]
    public string Body { get; set; }       

    public foo(IDataRecord record)
    {
        Hydrate(record, this);           
    }

    public foo() {}
}

 

Explanation:

- Class foo inherits from SharedBase specifying itself as the type. (NOTE SharedBase is abstract here in the event we want to provide additional methods which could be overridden by the instance class)

public class foo : SharedBase<foo>

- One of the foo class constructors accepts a data record which then calls the Hydrate method on SharedBase passing in the record and itself.

public foo(IDataRecord record)
{
     Hydrate(record, this);
}

- Hydrate method on SharedBase will use reflection on the object passed in to determine its properties. At the same time, it will effectively cache these properties to avoid repeated expensive reflection calls

public static void Hydrate(IDataRecord dataRecord, T instance)
{
     var instanceType = instance.GetType();

     //Caching properties to avoid repeated calls to GetProperties.
     //Noticable performance gains when processing same types repeatedly.
     if (cachedProperties == null)
     {
          cachedProperties = instanceType.GetProperties().ToList();
     }

.

.

.

- Hydrate method on SharedBase will iterate each property on the object and determine if a column with matching name exists in data record

foreach (var property in cachedProperties)
{
     if (!dataRecord.ColumnExists(property.Name)) continue;

     var ordinal = dataRecord.GetOrdinal(property.Name);

.

.

.

NOTE: ColumnExists is an extension method I put on IDataRecord which I’ll include at the end of this post.

- Hydrate method will determine if the property is nullable and whether the value in the corresponding column of the data record has a null value

var isNullable = property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof (Nullable<>);

var isNull = dataRecord.IsDBNull(ordinal);

var propertyType = property.PropertyType;

.

.

.

 - If Hydrate method determines the property is nullable it will determine the underlying type and set propertyType accordingly

- Hydrate method will set the value of the property based upon the propertyType

 

That’s it!!!

 

The magic here is in a few places. First, you may have noticed the following:

public abstract class SharedBase<T> where T : SharedBase<T>

This says that SharedBase can be created with any type and that for each type it will have it’s own instance. This is important because of the static members within SharedBase. We want this behavior because we are caching the properties for each type. If we did not handle things in this way only 1 type could be cached at a time, or, we’d need to create a collection that allows us to cache the properties for each type = not very elegant.

 

Second, in the constructor for foo you may have noticed this (literally):

public foo(IDataRecord record)
{
     Hydrate(record, this);
}

I wanted the code for auto-hydrating to be as simple as possible. At first I wasn’t quite sure how I could call Hydrate on SharedBase within an instance of the class and pass in the instance itself. Fortunately simply passing in “this” does the trick. I wasn’t sure it would work until I tried it out, and fortunately it did.

 

So, to actually use this feature when utilizing ADO.NET you’d do something like the following:

 

     public List<foo> GetFoo(int? fooId)
        {
            List<foo> fooList;

            const string uspName = "usp_GetFoo";

            using (var conn = new SqlConnection(_dbConnection))
            using (var cmd = new SqlCommand(uspName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@FooID", SqlDbType.Int)
                                       {Direction = ParameterDirection.Input, Value = fooId});
                conn.Open();
                using (var dr = cmd.ExecuteReader())
                {
                    fooList= (from row in dr.Cast<DbDataRecord>()
                                            select
                                                new foo(row)
                                           ).ToList();
                }
            }

            return fooList;
        }

 

Nice! Instead of having line after line manually assigning values from data record to an object you simply create a new instance and pass in the data record. Note that there are certainly instances where columns returned from stored procedure do not always match up with property names. In this scenario you can still use the above method and simply do your manual assignments afterward.

 

I should show how this method of hydration is used in conjunction with an ADO call. See below:

public List<Template> GetFooList()
        {
            List<foo> list;

            const string uspName = "usp_GetFoo";

            using (var conn = new SqlConnection(_sqlConnection))
            using (var cmd = new SqlCommand(uspName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@fooID", SqlDbType.Int) { Direction = ParameterDirection.Input, Value = DBNull.Value });
                conn.Open();
                using (var dr = cmd.ExecuteReader())
                {
                    list = (from row in dr.Cast<DbDataRecord>()
                                  select new foo(row)
                                 ).ToList();
                }
            }

            return list;
        }

 

EDIT: Here’s the extension method I mentioned above which checks whether a given column exists in the data record…

 

/// <summary>
/// Extension method allowing quick check for existence of column.
/// </summary>
/// <param name="record"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static bool ColumnExists(this IDataRecord record, string columnName)
{
     for (var i = 0; i < record.FieldCount; i++)
     {
          var tempName = record.GetName(i);
          if (columnName == tempName)
          {
               return true;
          }
     }
     return false;
}

Why isn't Visual Studio letting me debug JavaScript any longer?

In my particular case the problem was due to Silverlight. I have my ASP.NET MVC application which is incorporating a Silverlight application into it.

When integrating the Silverlight application VS will ask you if you want to allow for debugging your Silverlight application. Selecting yes will enable the Silverlight debugger in your web applications project settings (see below):

To allow JavaScript debugging again simply uncheck the Silverlight checkbox and start your application.

 

Finished Chicago Marathon 2011!

Sunday I completed my 3rd Chicago Marathon and set a new PR (3:26:01)! It was an incredible day! Especially motivating was seeing my family at mile 15 and giving sweaty teary-eyed hugs to everyone. 

I doubt I'll ever forget seeing my wife and son at mile 15 along with my in-laws and their son cheering me on. I especially will not forget having my son run up to me after the race and giving me a huge hug. Simply awesome!!!

 

I cannot thank my wife enough for being so supportive of me during the past few months with all of the training even on weekends. The training can get pretty monotonous not to mention both physically and mentally exhausting. It's great to have family support at home to keep me going.

What's that "using" keyword used for again???

I was asked this question today and was unable to give a truly correct answer which bothered me. So, using this handle tool called the "internet" I quickly found an oldie but goodie that filled me in www.codeproject.com/KB/cs/tinguusingstatement.aspx

I've seen people make use of the "using" keyword in samples I've found online in the past but never took the time to figure out why. I always presumed it was shorthand for referencing a particular objects properties & methods without having to go line by line. Turns out this keyword prevents you from necessarily having to write a try/catch/finally block in order to dispose of the object/resource when finished.

In hindsight I really should have looked this up sooner. For the longest time I've added try/catch/finally blocks sometimes specifically so that I could dispose of certain resources when finished (i.e. I/O, database, etc.).

In the words of the late Chris Farley in Black Sheep..."boy do i feel like a horse's patoot!"

Entity Framework 4 - When you can use Parameterized Constructors in Select

Skip to the bottom if want the quick answer ;-)

Just a quick note on something I ran into today...

I've taken to utilizing a View Model in which objects are created on a 1 to 1 basis to hold entities supplying data to the View/UI.  As such, I modified my existing linq queries such that the View Model objects are created in the select portion of the query resulting in an enumerable list I can supply to the View.  Below is an example:

IQueryable<sessionfoo> fooquery = ((IEnumerable<sessionfoo>)Session["foo"]).AsQueryable();

IQueryable<fooViewModel> result = from f in fooquery where f.isFooey == true select new fooViewModel(f); 

I am relatively new to the Entity Framework, so it is totally possible there is a better way of accomplishing all this, however, it illustrates the point.  You can see above that I am pulling out my list of entity objects from session and dumping them into an IQueryable instance.  From this point, I execute a Linq to Entity query which allows me to utilize a parameterized constructor in the select.

Later in the day I tried this same approach with a query which was Ling to SQL and that's where I was promptly informed by the runtime that this is not permissible. If you need to get around this inconvenience simply call ToList() on your query, then call AsQueryable() which now allows you to execute Linq to Entity queries on the original Linq to SQL result. 

So, you can use parameterized constructors for Linq to Objects, but not for Linq to Entities