In the project, you must use various ORM types, such as NH, EF, and fluent Data. However, as I have been using ORM over the past few years, as the database structure becomes more complex and custom queries become more and more popular, a problem that has not been solved yet is the custom query, each time you encounter a custom query, You need to manually create a business entity to carry the result set of the custom query.
A piece of code was suddenly found in Xiao Qin's blog last week, which highlighted me:
Public IEnumerable <dynamic> Get () {// construct the query parameter var pQuery = ParamQuery. instance (). select (". *, B. permissionName as ParentName "). from (@ "sys_permission A left join sys_permission B on B. permissionCode =. parentCode "); // call the common methods in the service base class to return the query result return service. getDynamicList (pQuery );}
I used IEnmuerable <dynamic> to solve the problem of the result set of the custom query. Then I consulted Xiao Qin, who used fluent Data ORM. This framework supports IEnumerable <dynamic>, EF does not. Therefore, when using a custom query: 1. You can create a business entity to carry the result set. 2. Use dynamic linq to solve the problem. Since ORM does not support it, use SqlHelper to return the DataTable. Use dynamic linq to Solve the Problem Based on the DataTable. Think about it if it is a data management platform, it is certain that most of the functions are reports and queries, which is a big problem. If a project team has 10 members, it would be abnormal for everyone to create several business entities.
DataTable dt = GetDataTable();var query = from p in dt.AsEnumerable() select new { ID=Convert.toInt32(p["StuID"]), Name=p["StuName"].toString(), Chinese=Convert.toInt32(p["Chinese"]) };
Finally, IEnumerable <dynamic> is returned. When writing this demo, I first use ID = p. field <int> ("StuID"), and then prompts that the conversion is invalid when traversing the data, p ["StuID"] is used. In fact, p is a DataRow object. This process only saves the trouble of creating a custom Model.
Complete method:
public IEnumerable<dynamic> Get(){DataTable dt = GetDataTable();var query = from p in dt.AsEnumerable() select new { ID=Convert.toInt32(p["StuID"]), Name=p["StuName"].toString(), Chinese=Convert.toInt32(p["Chinese"]) }; return query;}
It's just the problems encountered during my work and the solutions I have come up with. Thanks for your advice. At the same time, I guess Microsoft will upgrade this function in future versions?