Entity Framework Lookup Tables

Question

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?

• JeffO: You should be able to find solutions to this on stackoverflow by searching for "entity framework lookup tables"
• brhardwick: I have tried. It looks like its all done through linq queries. Is this true?
• Robert Harvey: What you describe as a REF/Lookup table sounds like a simple JOIN/Relationship to me. Entity Framework handles such things without breaking a sweat. Simply define a relationship between the two tables.
• brhardwick: I dug into it a little more and managed to do joins based on Linq queries from models so i guess that worked. But im still biased towards datasets ;)

Answer 1

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);
 }

• Kyle J V: If you are used to creating your own SQL statements and still want something to map to entities (classes), check out Dapper github.com/StackExchange/dapper-dot-net