Friday, August 30, 2013

Getting the database name from an Entity Framework Context

This seems like it should be so easy, but it took awhile to figure out.  Both methods below take an ObjectContext.  You can get that from a DbContext using ((IObjectContextAdapter)myDbContext).ObjectContext.


public static string ADOConnectionString( ObjectContext context )
{
    return ( (EntityConnection)context.Connection ).StoreConnection.ConnectionString;
}

/// This works regardless of how the connection string names the database ("initial catalog", "database", etc.).
public static string DatabaseName( ObjectContext context )
{
    return new SqlConnectionStringBuilder( ADOConnectionString( context ) ).InitialCatalog;
}

Thursday, August 15, 2013

Querying an Entity Framework Model for Column Type Information

In the process of writing an import system for importing data into a database modeled by EF, I needed to be able to grab table/column information from the model.  Following is a method I wrote for doing that (it's more difficult than it seems).

First, here is the class I'm populating in the main method.  Of course, you could add properties to this as necessary:

Update:  There is a bug in the code below.  It's impossible to edit the (pasted in) HTML now.  the select after "var foreignKeyNames" below should be:
select ( p.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One ) ? 
( (AssociationType)p.RelationshipType ).ReferentialConstraints[0].FromProperties[0].Name :
( (AssociationType)p.RelationshipType ).ReferentialConstraints[0].ToProperties[0].Name );

public class EntityTypeInfo
{
    public string EntityName { get; set; }
    public string PropertyName { get; set; }
    public Type EntityType { get; set; }

    ///
    /// Identity, Computed columns
    ///
    public bool StoreGenerated { get; set; }
    public bool IsForeignKey { get; set; }
}

Now the main method along with a helper method (see update above):
  1. /// <summary>
  2. /// For the given table/entity list, build a list of type info.
  3. /// </summary>
  4. public static List<EntityTypeInfo> SimplePropertiesFrom( ObjectContext context, List<string> tables )
  5. {
  6.     var result = new List<EntityTypeInfo>();
  7.  
  8.     /// We have to draw from two different spaces to get all the info we need.
  9.     var conceptualMetadata = context.MetadataWorkspace.GetItems( DataSpace.CSpace );
  10.     /// context.MetadataWorkspace.GetItems() for storage space only works if queries have already run which we can't ensure here.
  11.     var storageMetadata = ( (EntityConnection)context.Connection ).GetMetadataWorkspace().GetItems( DataSpace.SSpace );
  12.  
  13.     var query = from c in conceptualMetadata
  14.                 join s in storageMetadata on
  15.                     c.BuiltInTypeKind == BuiltInTypeKind.EntityType ? ( (EntityType)c ).Name : "X" // If the item isn't an EntityType, we don't want a join, so just make these 2 values different
  16.                     equals
  17.                     s.BuiltInTypeKind == BuiltInTypeKind.EntityType ? ( (EntityType)s ).Name : ""
  18.                 where c.BuiltInTypeKind == BuiltInTypeKind.EntityType
  19.                 select new { conceptual = c as EntityType, storage = s as EntityType };
  20.     query.ToList().ForEach( field =>
  21.     {
  22.         if ( tables.Contains( field.conceptual.Name ) )
  23.         {
  24.             var foreignKeyProps = ( from p in field.conceptual.NavigationProperties
  25.                                     where p.RelationshipType is AssociationType && ((AssociationType)p.RelationshipType).IsForeignKey
  26.                                     select ( ( AssociationType )p.RelationshipType ).ReferentialConstraints[0].FromProperties[0].Name );
  27.             foreach ( var p in field.conceptual.Properties )
  28.             {
  29.                 result.Add( new EntityTypeInfo
  30.                 {
  31.                     EntityName = field.conceptual.Name,
  32.                     PropertyName = p.Name,
  33.                     EntityType = ( (PrimitiveType)p.TypeUsage.EdmType ).ClrEquivalentType,
  34.                     StoreGenerated = IsStoreGenerated( p, field.storage ),
  35.                     IsForeignKey = foreignKeyProps.Contains( p.Name )
  36.                 } );
  37.             }
  38.         }
  39.     } );
  40.     return result;
  41. }
  42.  
  43. private static bool IsStoreGenerated( EdmProperty conceptualEntityProperty, EntityType storageEntityType )
  44. {
  45.     EdmMember storageProperty;
  46.     storageEntityType.Members.TryGetValue( conceptualEntityProperty.Name, true, out storageProperty );
  47.     if ( storageProperty == null )
  48.     {
  49.         return false;
  50.     }
  51.     else
  52.     {
  53.         Facet f;
  54.         if ( storageProperty.TypeUsage.Facets.TryGetValue( "StoreGeneratedPattern", false, out f) )
  55.         {
  56.             return ( ( (StoreGeneratedPattern)f.Value ) == StoreGeneratedPattern.Identity ) || ( ( (StoreGeneratedPattern)f.Value ) == StoreGeneratedPattern.Computed );
  57.         }
  58.         else
  59.         {
  60.             /// If it's not store generated, the above property won't be there (StoreGeneratedPattern.None is never referenced).
  61.             return false;
  62.         }
  63.     }
  64. }