none
Lookup tables, enums, EF5 question

    질문

  • Hello all

    I'm working on MVC4 project which uses EF5 and SQL2012 as DB. First thing first, here is my table structure

    DictionaryTable:

    DictID     int
    RUWordID   int  
    -> FK to RUWordsTable.RUWordID
    ENWordID   int  
    -> FK to ENWordsTable.ENWordID

    RUWordTable
    :

    RUWordID   int
    Word       string

    ENWordTable
    :

    ENWordID   int
    Word       string

    I want be able read/write data to table that uses following structure:

    RUWord   ENWord
    Привет   Hello
    ...

    By default EF omits junction table (DictionaryTable) and creates two entities for RuWords and EnWords tables. There is opposite of what I'm trying to get. In MS access I can easily create lookup table, but this is another story and Access is comination of front-end and back-end in one interface. Also EF5 will out soon and in internet/forums still few documentation/eamples about new features. After reading some documentation I found that there is a lot products features to use. For example:

    1) I can create editable views (using INSTEAD OF trigger on INSERT/UPDATE/DELETE) operations. It will give my what I want in entities, but I need to manuallly code all operation in SQL

    2) I can go on higher level and do all stuff in EF (here is a lack of knowlege). And that a point where i'm stucks as there several ways/features like Code-First, Enum types (I can potentially try to bind it to lookup table, EnWords for example). Also looks like DbSet is recommended way to work with EF. And there is still a lot of stuff that doing the same but still lacks simplicity for beginners

    3) Do all stuff in code in MVC application, just create dummy ID columnt in my DictionaryTable. This will add it as separate table to EF designer and will create one-to-many relationships

    There is a lot of similar quetions, but most of them outdated and not using new feature from EF5 which may be useful or do the same in easier way. If someone will point my right way I'll be much appreciated. Btw I'm using Database first as this approach most convenient for me and I don't like to deal with migrations tables and so on. I heard that classes can be generated from existing datbase, but not tried to do that as proble not here now


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    2012년 7월 29일 일요일 오후 5:06

답변

  • I've fixed this problem by creating stored procedures and importing them into entity designer. Then mapped to corresponding operations (insert, update, delete) on my view, which is also imported. Using INSTED OF INSERT/UPDATE/DELETE trigger not possible because not sees it in database and basically EF requires to have mapping to stored procedures for operations of data manipulation on view

    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    • 답변으로 표시됨 Sergey Sypalo 2012년 8월 21일 화요일 오전 11:45
    2012년 8월 21일 화요일 오전 11:45

모든 응답

  • Hi Sergey,

    Thank you for your post.

    I am not sure if what you want is mapping these three tables to a single table or not. If so, you can use the Inheritance with EF Code First to achieve this. Please check this blog: http://weblogs.asp.net/manavi/archive/2010/12/24/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph.aspx

    If I misunderstood anything, please feel free to let me know.

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    2012년 7월 30일 월요일 오전 9:34
  • Hi Alexander,

    No, DictionaryTable is just usual junction table to store mapping between words and it's translations. So there is not something related to inheritance. As i mentioned i'm trying to get entilty that just have two properties: EnWord and RuWord and have ability to add and remove word pairs. EF or SQL or whatever should check if word already exists in underlying table(s) and if so - use existing ID and if not create new one and add/remove mappings in Dictionary table. Per my understanding if I will go with EF and decide to implement logic here, probably I can/must use T4 templates and POCO generator to create classes, not sure about this. Also i prefer to stay with database first model. And is it really that EF can't have entites without defined primary key?


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    2012년 7월 30일 월요일 오후 12:40
  • Hi Sergey,

    Specifically, what you want to do is adding and removing EnWord and RuWord properties as a pair. Is that right? For example, if you add a new pair, property EnWord and RuWord each will have a new value. If I misunderstood anything, please feel free to let me know.

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    2012년 8월 1일 수요일 오전 9:32
  • Correct. I'm figuread out how to configure INSTEAD OF trigger on View to allow adding new word pairs, but stuck pn another problem that EF requires to have primary key on table/view, otherwise entity will be read-only. I was added column to DictID from Dictionary table to veiw, added clustered index to it but problem the same - EF doesn't seen primary key even if I not need it at all. Not sure if that problem exist if I'll move to CodeFirst

    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    2012년 8월 1일 수요일 오후 12:39
  • Hi Sergey,

    I think you can create a update and delete method. For example:

            static void Main(string[] args)
            {
                using (var context = new TestDatabaseEntities())
                {
                    AddWords("an", "not", context);
    
                    Console.ReadLine();
                }
            }
            public static void AddWords(String ruWord, String enWord, TestDatabaseEntities context)
            {
                Int32 i = 0;
                var rwIsExists = context.RUWordTables.Where(p => p.Word == ruWord).Count();
                if (rwIsExists == 0)
                {
                    RUWordTable ruObj = new RUWordTable
                    {
                        Word = ruWord
                    };
                    context.RUWordTables.AddObject(ruObj);
                    ENWordTable enObj = new ENWordTable
                    {
                        Word = enWord
                    };
                    context.ENWordTables.AddObject(enObj);
                    DictionaryTable dObj = new DictionaryTable
                    {
                        ENWordTable = enObj,
                        RUWordTable = ruObj
                    };
                    context.DictionaryTables.AddObject(dObj);
                    try
                    {
                        i = context.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                    finally
                    {
                        Console.WriteLine(i);
                    }
                }
            }

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    2012년 8월 2일 목요일 오전 8:42
  • That's how it's working now, but as I mentioned, more preffered method to have one entity instead of three(if we'll use dummy column in join table) or two.

    I've tried to create view that contains following fields:

    DictionaryTable.DictID

    RuWordTable.Word

    EnWordTable.Word

    That I've added INSTEAD OF INSERT trigger to that view to have ability add data through view and it works fine if i'm adding data using SQL Management Studio. But Visual Studio tells me that view added to model as read-only because don't have primary key defined. During testing it's trow an exception that I need to create procedures or functions for inserting/deleting data in SQL and map it to this view in EF designer. I'm a bit confused why it's so complicated to do such simple stuff.

    Another one question: is there any way to override entities creation? For example create views and other stuff in SQL, but do everything in EF. I'd like to have an entity with built in functionaity for lookup in tables that connected using foreign keys. At least it simplifies further development, because I can created controller with CRUID functionality based on this entilty instead of coding it manually.


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security


    • 편집됨 Sergey Sypalo 2012년 8월 3일 금요일 오전 7:33
    2012년 8월 3일 금요일 오전 7:32
  • Hi Sergey,

    If you want to add or update data of view, you can try steps in the following page: http://blogs.msdn.com/b/rickandy/archive/2008/10/04/how-to-create-an-updateable-view-with-ado-entity-framework.aspx

    For your second question, if you use Code First, it is convenient to create entities as you like. But, for Database First, you should be careful, because the model was created before you can modify. You can add the additional method in corresponding classes (entities), but if you modify the properties in the class, perhaps its mapping relationship will be changed.

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    2012년 8월 6일 월요일 오전 8:54
  • Hi Alexander,

    Main problem with manual editing of SSDL, is that any manual changes will be overwritten on next model update. I'm looking for solution CodeFirst/DatabaseFirst to be able create entity that maps to Dictionary table but instead of providing ID for corresponding words it should look for it in table.

    As per my understanding { get; set; } after properties in some method of class allows to use scaffolding while generating views and also in case of using CodeFirst it will generate corresponding colums in table, I mean if I'll create following class:

    public class MyEntity

    {

    public string ID { get; set; }

    public string Word { get; set ;}

    }

    EF will generate table with two fields, one of them ID as PK and second is varchar. But my idea to create entity and replace default { get; set; } to something like

    get {

    return ... where Wordid=this.ID

    }

    Not sure exactly how to do that, and what types of field is should be, because in my DictionaryTable fields RuWordID and EnwordID have type int, but I want to return string (corresponding word with that ID from linked table)


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    2012년 8월 7일 화요일 오전 8:14
  • I've fixed this problem by creating stored procedures and importing them into entity designer. Then mapped to corresponding operations (insert, update, delete) on my view, which is also imported. Using INSTED OF INSERT/UPDATE/DELETE trigger not possible because not sees it in database and basically EF requires to have mapping to stored procedures for operations of data manipulation on view

    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    • 답변으로 표시됨 Sergey Sypalo 2012년 8월 21일 화요일 오전 11:45
    2012년 8월 21일 화요일 오전 11:45