Database – Entity Framework Lookup Tables

databaseentity-framework

I am coming from working with ADO.net DataSets and feel fairly comfortable with how they work. Mainly, I usually have a DATA table with a large number of REF tables for lookups.

In DataSets it was fairly simple to define a query and some access functions to get a REF table bound to a dataset or a class. I could also create a table in the dataset that was comprised of a large number of tables joined together.

However, this seems like not the norm in the entity structure. Usually in tutorials, I see people adding entries to a table without any lookups which does not work for my database model.

What are my misconceptions and assumptions here?

Best Answer

For implicit relationship generation when creating a database-first model in Entity Framework, the tables must have primary keys and reference the other tables' primary keys with foreign keys.

If you like having the joins kept inside in the SQL layer (in code, I meant, most times LINQ creates a SQL join in the statement), create a stored procedure and map that procedure in EF, which can just get called as a function call once mapped. The select results from the procedure would map to an IEnumerable of "usp_myprocnameResults" entity.

A nice little example: http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

using (var context = new SchoolDBEntities())
        {
            var courses = context.GetCoursesByStudentId(1);

            foreach (Course cs in courses)
                Console.WriteLine(cs.CourseName);
        }
Related Topic