Accessing a Common Code Value Table in Generated EF Models

I have a database that uses a custom model for recording lookup valus (i.e. states, types, etc.). So across all of the database tables, there are various columns called something like state_cdv_id which would store an integer and reference the code_value table to get that state's value (i.e. "CA", "AK", etc.).

I would like to map my EF model so that I can access the code values for all of these fields, and I don't want to have to do it manually in partial classes for EVERY entity... that's a lot of repetition. So I want to be able to access my code values like: MyPerson.State and get back the string "CA" for example.

Here's what a single getter would be that I would have to repeat many times if I were to do it manually:

    public string State
    {
        get
        {
            MyEntityContext c = new MyEntityContext();
            return c.CodeValues.Single(cv => cv.Id == RecordStatusCdvId).Value;
        }
    }

I don't know what the best approach would be: change the T4 templates, add prope开发者_如何转开发rty attributes to certain fields and then programmatically add a getting to those, or something else.

Any help?

If there is a 1:1 relationship between the entity and the code_value table the entity should already have a State property, which by default which will be null by default, you could then fill it in by using an Include on your DB queries:

var foo = context.MyEntities.Include( x => x.State);

Your sample code is terribly wrong because it makes your entity dependent on the context (moreover you don't dispose it). Whole POCO approach just to avoid this (POCO T4 generator and DbContext T4 generator).

If you have relation to lookup table in your database EF will crate for you navigation property. If you don't have such relation in the database and you are using EDMX file you can still create such relation in your model and you will again get navigation property to lookup table. Once you have navigation property you can simply do:

string code = myEntity.State.Code;

But the navigation property must be loaded either by eager loading (as @BrokenGlass described) or by lazy loading.

If you don't like the idea of navigation property and you still want State property to show just code of the state you must understand what does it mean: If you map the entity that way it will be read-only because EF will not be able to transform compound entity back to real tables which must be updated. It is possible to map the entity the way you want but it is considered as advanced (and mostly not needed) scenario which works only if you have EDMX file (not with code first approach). The choices are:

• Create database view and map the view to a new entity
• Create DefiningQuery manually in EDMX (opened as XML) file and map it to a new entity (once you do that you cannot update your model from database or generate database from model any more)
• Create QueryView manually in EDMX (opened as XML) file and map it to a new entity (this requires original entities to be already mapped)

You will have to do that for every table where you want such mapping. Anyway whole that complexity with manually changing EDMX is not needed because you can simply create custom classes like:

public class SomeViewModel // I suppose your main target is to have codes in presentation layer
{
    public string SomeData { get; set; }
    public string State { get; set; }
}

and use projection query

If you have navigation property:

var data = from x in context.SomeEntities
           select new SomeViewModel
               {
                   SomeData = x.SomeData,
                   State = x.State.Code  
               };

If you don't have navigation property

var data = from x in context.SomeEntities
           join y in context.LookupValues on x.LookupId equals y.Id
           select new SomeViewModel
               {
                   SomeData = x.SomeData,
                   State = y.Code  
               };