The SQL statement generated when using the generic data type in the Linq To EF clause queries the entire table.

The SQL statement generated when using the generic data type in the Linq To EF clause queries the entire table.
1. Symptoms

 public class LinqHepler<T> where T:class    {        private EFDBContext _context = null;        /// <summary>        ///         /// </summary>        /// <param name="context"></param>        public LinqHepler(EFDBContext context)        {            _context = context;               }        /// <summary>        ///         /// </summary>        /// <param name="whereLambda"></param>        /// <returns></returns>        public IQueryable<T> LoadEntities(Func<T, bool> whereLambda)        {            return _context.Set<T>().Where(whereLambda).AsQueryable();        }        /// <summary>        ///         /// </summary>        /// <param name="whereLambda"></param>        /// <returns></returns>        public IQueryable<T> LoadEntitiesExpression(Expression<Func<T, bool>> whereLambda)        {            return _context.Set<T>().Where(whereLambda).AsQueryable();        }    }

 

[HttpGet] [Route ("LinqWhere")] public IActionResult LinqWhere () {string uname = ""; _ context. userInfo. where (c => c. userName = uname ). toList (); // LinqHepler with conditions <UserInfo> linqHepler = new LinqHepler <UserInfo> (_ context); linqHepler. loadEntities (c => c. userName = uname ). toList (); // query the entire linqHepler table. loadEntitiesExpression (c => c. userName = uname ). toList (); // do not check the full table return OK ();}

Production SQL

2017-10-11T02: 59: 22.495010Z 2328 Query SELECT 'C'. 'userid', 'c'. 'username', 'c'. 'userpwd'
FROM 'userinfo' AS 'C'
WHERE 'C'. 'username' =''
2017-10-11T02: 59: 22.538041Z 2328 QuerySELECT 'u'. 'userid', 'u'. 'username', 'u'. 'userpwd'
FROM 'userinfo' AS 'U'
2017-10-11T02: 59: 22.551050Z 2328 QuerySELECT 'C'. 'userid', 'c'. 'username', 'c'. 'userpwd'
FROM 'userinfo' AS 'C'
WHERE 'C'. 'username' =''

 

It can be seen that the first one is to use the EF object to directly call the extension method of Linq Where to generate the corresponding Where condition,

The second one uses the generic type to pass the object type to be queried and the Where condition to query. At this time, the SQL statement corresponding to the Where condition is not produced.

Third, the Expession type packaging Func object has production Where SQL

 

Refer to the interpretation of great god

Yuan You asked: the SQL generated by EF when writing linq does not contain the where condition. The SQL statements are displayed for all tables. What is the reason?

0
Reward garden beans: 50 [solved problems] views: 575
My data warehouse is used. My query of linq is as follows:
// Query
Public IQueryable <T> Query (Func <T, bool> wherelambda)
{
Return db. Set <T> (). AsNoTracking <T> (). Where <T> (wherelaming). AsQueryable ();
}
My linq calls this query method, but the SQL generated by blocking through ef SQL is a full table scan, and the SQL generated by monitoring the SQL profile does not contain SQL

A: The macro explanation is that Expression has a special internal parser privoder, which will be parsed into the corresponding SQL and executed to the database according to the condition, func, the broken delegate type, does not have such a professional SQL parser and cannot generate the SQL you want. It simply waits for the default full table to be loaded and filtered in the memory.

Summary

The framework is exclusive, and the results show that there is a big problem. It may be that the method of the previous search question is incorrect, or the question is too simple to find the answer for a long time, it turns out to be the basic knowledge of linq;

I still can't copy it. I later checked that all the method parameters in linq contain this keyword, and I hope it will be helpful to everyone;