I am working on an example project that uses the Northwind database and the EDM and ASP. NET MVC1.0 frameworks. The project has just started and is currently undergoing some technical tests. The problem arises when the entity layer is operated.
Problem: When querying an object set, we usually do not need to query all the fields, but return this object or object combination. What should we do?
For example, the Categories table (which is the Category class in EDM) contains the Picture field. Picture stores images and the data is relatively large, it is not efficient to retrieve all the data in this table every time.
Solution Process:
First, you can use Entity SQL to query specific fields.
For example, we only need to query the CategoryID and CategoryName fields of Category.
We can do this:
String esqlCommand = "SELECT c. CategoryID, c. CategoryName FROM NorthwindEntities. Category AS c ";
Using (NorthwindEntities northwind = new NorthwindEntities ())
{
List <Object> readers = northwind. CreateQuery <Object> (esqlCommand). ToList ();
// List <Obect> readers = (from c in northwind. Category select new {CategoryID = c. CategoryID, CategoryName = c. CategoryName). ToList (); method 2
// The northwind. CreateQuery <Category> cannot be used here, because the returned result is an anonymous type after the esqlCommand is executed, and it cannot be converted to the Entity type.
// This is the key to the problem.
}
The first thought is:
Class MyCategory: Category {} // The MyCategory class inherits the attributes of the Category class, so that I can make the result return MyCategory
String esqlCommand = "SELECT c. CategoryID, c. CategoryName FROM NorthwindEntities. Category AS c ";
Using (NorthwindEntities northwind = new NorthwindEntities ())
{
List <MyCategory> readers = (from c in northwind. Category select new MyCategory {CategoryID = c. CategoryID, CategoryName = c. CategoryName). ToList ();
// The northwind. CreateQuery <Category> cannot be used here, because the returned result is an anonymous type after the esqlCommand is executed, and it cannot be converted to the Entity type.
// This is the key to the problem.
}
OK, but in the future, you need to add various myclasses for each class, and this class also makes the entity layer not "clean ". At this time, I thought of using reflection to convert the result into the entity we want, and reading dudu's CN. Text Development notes online-reading data into the entity Class Using Reflection. The following method is written based on dudu.
Public class Shared
{
Private static void ReaderToEntity (IDataRecord reader, Object entity)
{
For (int I = 0; I <reader. FieldCount; I ++)
{
System. Reflection. PropertyInfo propertyInfo = entity. GetType (). GetProperty (reader. GetName (I ));
If (propertyInfo! = Null)
{
If (reader. GetValue (I )! = DBNull. Value)
{
PropertyInfo. SetValue (entity, reader. GetValue (I), null );
}
}
}
}
Public static T ExecToEntity <T> (string esqlCommand)
{
DbDataRecord reader;
Using (NorthwindEntities northwind = new NorthwindEntities ())
{
Reader = northwind. CreateQuery <DbDataRecord> (esqlCommand). FirstOrDefault ();
}
If (reader = null)
{
Return default (T );
}
Else
{
T entity = (T) Activator. CreateInstance (typeof (T ));
ReaderToEntity (reader, entity );
Return entity;
}
}
Public static List <T> ExecToEntities <T> (string esqlCommand)
& N