Entity framework: Database first/Code first hybrid

Question

I am trying to create a custom Entity Framework (4.2) entity that would be mapped to my database like it would be done in a Code first approach.

The issue is that my entity framework data model is using Database first.

How can I add my custom entity to entity framework's context?

Answer 1

If by the Database first you mean that you already have EDMX created from exiting database you simply cannot use code first. You must create table and update model (EDMX) from the database to include it in EDMX.

Edit based on comment:

I want to create a BriefUser entity that would basically be a lighter version of User but it would have properties retrieved from User's foreign keys.

Well this is possible. You can either create BriefUser as common class and use projection in query.

var breifUser = (from x in context.Users
                 where ...
                 select new BriefUser
                 {
                     // Fill BreifUser's properties here
                 }).FirstOrDefault();

You can even refactor former code to reusable extension method:

public static IQueryable<BriefUser> ProjectUser(this IQueryable<User> query)
{
    return query.Select(x => new BreifUser() 
                             { // Fill BreifUser's properties here });
}

and use it like:

var briefUser = context.Users.ProjectUser().FirstOrDefault(...);

It is also possible to define your new class as "entity view". The first problem is that each table can be mapped to only one entity (except some advanced concepts like inheritance or splitting) so you cannot define your BriefUser as a new entity type because mapping both User and BriefUser to UserTbl would violate this rule. You must use special construct called QueryView.

QueryView is view in mapping level. It allows you to create new mapped type which is projection of existing mapped entities defined directly in EDMX's MSL part. The projection is defined as custom Entity SQL query. The problem is that QueryView has limitations:

• It doesn't offer all Entity SQL features - for example it doesn't support aggregations (which I consider as really missing feature). Without aggregations you for example cannot create a new type which will contain property counting some related entities.
• It is not supported in designer. You must edit your EDMX as XML to define QueryView and you must write Entity SQL query yourselves.
• Resulting type is a "view" and it is read-only.

I want to keep the EDMX file, but also be able to add an entity (BriefUser) to EF's context.

This is not possible. Your BreifUser is only projection / view and EF is not able to track changes back to originating tables so you cannot add BreifUser to context and persist it. In case of QueryView you can achieve it if you define custom stored procedures which will no how to decompose BreifUser and modify all related tables. These stored procedures must be imported to the EDMX and mapped to data modification operations of the view entity. Btw. same will happen if you map your entity to the database view because EF takes all views as read-only.