Entity Framework - View or attach a database in Linq to objects

Question

I have a database table with many lookup tables:

OrderType ShippingType etc. 

My order table refers to each of these tables:

 Order OrderID OrderTypeID ShippingTypeID 

I am using Entity Framework as a data access layer. I have a page that should display order information. I'm struggling to find the best / right way to use these objects.

My page should display data such as:

Order No. 1000000
Shipping Type: UPS
Order Type: Online
Etc Type: Etc.

Is it better to create a view in the database that returns the data I need and then add it to my entity model and just use it directly so that I don't have to write joins in my queries? Or it is better to create an intermediate class as follows:

 class OrderView { public int OrderNumber { get; set; } public string OrderType { get; set; } public string ShippingType { get; set; } } var order = from o in db.Orders join ot in db.OrderTypes on o.OrderTypeID equals ot.OrderTypeID join st in db.ShippingTypes on o.ShippingTypeID equals st.ShippingTypeID select new OrderView { OrderNumber = o.OrderNumber, ShippingType = st.Description, OrderType = ot.Description }; 

What is the best way here?

Answer 1

You do not need to join as such. You can use the Navigation Properties for OrderType and ShippingType to access them without the need for combining. You will have something like:

 var order = from o in db.Orders select new OrderView { OrderNumber = o.OrderNumber, ShippingType = o.ShippingType.Description, OrderType = o.OrderType.Description }; 

I see no advantage for this in the view.

Answer 2

You can use Linq-to-entity. I usually switch to SQL or View when I have something that I cannot write to L2E (like Common table expression and hiearchical queries) or when L2E performance is poor. If you do not have these problems, you should be happy with L2E.

Btw. your request can be rewritten without combining - damn @Craig was faster.

Answer 3

Views can be good for several reasons:

• They can isolate you from changes in the basic structure of the table.
• They can abstract the details of normalization (joins)
• Yoi can revoke all permissions on tables and provide limited access through views.

Do everything that leads to clear code.

Answer 4

Using a database view can interfere with SQL optimizer's ability to get a better execution plan. therefore, if an EF-generated query is not a horror (use SQL Profiler to make sure!) I will go for EF.