Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EntityFramework detecting complex type with database first

We are using EntityFramework and have already gone through a long journey with Database First. We have multiple contexts and many, many entities. While we entertain the notion of undergoing a project of code first, we want to explore the alternate possibilities available with EF.

We are using the latest version of EF as of current, 6.0.2

We have quite a few tables with common fields such as Auditing fields "CreatedBy", "CreatedDate", "UpdatedBy" and "UpdatedDate" and we thought that it would be perfect to use a ComplexType here.

In a table that uses those fields, when we generate the code from the database, it pulls the fields in raw. We then delete it in the model browser, add the complex type and then maps the complex properties to the field names in the DB.

In this experiment, we ran the "Generate database from model" without mapping the fields to see what were the resulting column names and if any convention or auto-magic would allow that behavior bi-directionally(Turning complex type into columns and recognizing columns as complex types).

This resulted in column names with the complex type suffixed with an '_' and the field name. We tested and it didn't pull the complex type back from the database in the model when we regenerated the model.

Is there a proper way to have EF detect Complex types in tables with database first? Is there some code factory, builder, strategy or template that I can write?

Our main motivation is that we have quite a number of tables to support, we embrace frequent changes and we want to prevent people on our team neglecting this step and breaking the code base.

Much appreciation for your time StackOverflowians!

-- Edit --

While this doesn't solve the problem using the auto-detection of complex types, this has resolve the issues of Developer's breaking the model each time they run the T4 template updates.

http://www.ninjacrab.com/2015/02/07/update-entityframework-detecting-complex-type-with-database-first/

like image 591
Min Avatar asked Feb 07 '14 14:02

Min


People also ask

What is complex type in Entity Framework?

Entity Framework Tutorial ComplexType. The Complex types are non-scalar properties of entity types that enable scalar properties to be organized within entities. ComplexType doesn't have keys and therefore cannot exist independently. It can only exist as properties of entity types or other complex types.

Can an entity type be used more than once in SQL?

It can even be used more than once in the same entity type. This entity type would then be stored in a table in the database that would look something like this. Of course, in this case, a 1:n association (Customer-Address) would be the preferred model, but the example shows how complex types can be used.

What are the limitations of ComplexType?

ComplexType doesn't have keys and therefore cannot exist independently. It can only exist as properties of entity types or other complex types. It cannot participate in associations and cannot contain navigation properties.

What is complex type in SQL Server?

A complex type allows you to map selected fields of a database table into a single type that is a child of the main type. [ComplexType] public class Address { public string Street { get; set; } public string Street_2 { get; set; } public string City { get; set; } public string State { get; set; } public string ZipCode { get; set; } }


1 Answers

What I did was a Generic semi-Repository pattern over EF. I also used intefaces to allow identification and automatic use of methods if they match.

Auditable Interface:

public interface IDbAuditable : IDbEntity
{
    Guid CreatedById { get; set; }
    DateTime CreatedOn { get; set; }
    Guid ModifiedById { get; set; } 
    DateTime ModifiedOn { get; set; }
    Guid? DeletedById { get; set; } 
    DateTime? DeletedOn { get; set; } 
}

(I personally like the idea that if CreatedOn == ModifiedOn then it's never been modified, some people like DateTime?, doesn't really matter they serve the same purpose.)

Since this example is in a small project, I simply wrapped the EF Context and didn't use any IoC/DI. This is a small subset of all the actual code (some methods are missing and some interfaces are missing, but it should make perfect sense).

public sealed class MyDb : IDisposable
{
    private MyContext _context;

    public MyDb(string connectionString, Lazy<Guid?> currentUserIdFunc)
    {
        this._context = new MyContext(connectionString);

        Database.SetInitializer<MyContext>(new DatabaseInitializer());
        this._context.Database.Initialize(true);

        this._currentUserIdFunc = currentUserIdFunc;
    }

    public async Task<T> GetEntityAsync<T>(Func<IQueryable<T>, IQueryable<T>> entityQuery) where T : class, IDbEntity
    {
        var query = entityQuery(this._context.Set<T>());

        if (typeof(T) is IDbAuditable)
        {
            query = query.Cast<IDbAuditable>()
                 .Where(a => !a.DeletedById.HasValue)
                 .Cast<T>();
        }

        return await query.FirstOrDefaultAsync();
    }

    public async Task<int> UpdateAsync<T>(T entity) where T : class, IDbEntity
    {
        if (entity is IDbDoNotModify)
        {
            throw new DoNotModifyException("Entity cannot be Modified (IDoNotModify).");
        }

        this._context.Set<T>().Attach(entity);
        var entry = this._context.Entry<T>(entity);
        entry.State = EntityState.Unchanged;

        var entityType = entity.GetType();

        var metadata = entityType.GetCustomAttributes(typeof(MetadataTypeAttribute)).FirstOrDefault() as MetadataTypeAttribute;
        if (metadata != null)
        {

            var type = metadata.MetadataClassType;

            var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
                 .Select(p => new
                 {
                     Name = p.Name,
                     ScaffoldColumn = p.GetCustomAttributes(typeof(ScaffoldColumnAttribute), true).FirstOrDefault() as ScaffoldColumnAttribute,
                     Readonly = entityType.GetProperty(p.Name).GetCustomAttributes(typeof(ReadOnlyAttribute), true).FirstOrDefault() as ReadOnlyAttribute
                 })
                 .Where(p => (p.ScaffoldColumn == null || p.ScaffoldColumn.Scaffold)
                     && (p.Readonly == null || !p.Readonly.IsReadOnly))
                 .ToList();

            foreach (var property in properties)
            {
                entry.Property(property.Name).IsModified = true;
            }

        }
        else
        {
            entry.State = EntityState.Modified;
        }

        var auditable = entity as IDbAuditable;
        if (auditable != null)
        {
            this.Modified(auditable, this._currentUserIdFunc.Value);
            entry.Property("ModifiedOn").IsModified = true;
            entry.Property("ModifiedById").IsModified = true;
        }


        return await this._context.SaveChangesAsync();
    }

    private void Modified(IDbAuditable instance, Guid? currentUserId)
    {
        instance.ModifiedById = currentUserId.Value;
        instance.ModifiedOn = DateTime.Now;
    }
}

Here is how I would use it:

// returns the first car with a model of ford
var car = MyDb.EntityAsync<Car>((query) = query
  .Where(c => c.Model.Equals("ford")
);

// returns the first dog with an ownerId of id
var car = MyDb.EntityAsync<Dog>((query) => query
  .Where(d => d.OwnerId == Id)
);

UpdateAsync(car);

This example application is very small so I didn't implement any UnitOfWork but it could be easily modified for such a case. Additionally, the MyDb class could be turned into a MyDb<T> if you have multiple Contexts pretty easily. I allow heavy use of DataAnnotations.

like image 77
Erik Philips Avatar answered Oct 30 '22 18:10

Erik Philips