When does Entity framework Linq query Hits Sql Databse?

Question

i'm new in entity framework.Below is my code,

So in my code i have created object of my db context and then i have a query 'queryForAuthentication' and in that i have used two tables 'conDb.SystemMasters' and joined with conDb.SystemAdminMasters , so will hit twice or how does it manage . i want to know when does entity framework will hit in to database ?

 QuizzrEntities conDb = new QuizzrEntities();
 List<OnLoginData> lstOnLogoonData = new List<OnLoginData>();
 string userpassWordHash = string.Empty;
 var queryForAuthentication =from systemObj in conDb.SystemMasters
                             where systemObj.StaffPin == dminLoginInput.StaffPin
                             join admin in conDb.SystemAdminMasters on systemObj.SystemId equals admin.SystemID
                             select new 
                            {
                             admin.PasswordSalt,
                             admin.PasswordHash, 
                             systemObj.StaffPin,
                             admin.UserName, 
                             admin.SystemID 
                            };
 if (queryForAuthentication.Count() > 0)
                    {
                        CheckStaffPin = true;
                        var GetUserUsingUsernamePasword = queryForAuthentication.Where(u => u.UserName.ToLower() == AdminLoginInput.UserName.ToLower());
                        if (GetUserUsingUsernamePasword.ToList().Count == 1)
                        {
                            checkuserName = true;
                            string DBPasswordSalt = queryForAuthentication.ToList()[0].PasswordSalt,
                                   DBPasswordHash = queryForAuthentication.ToList()[0].PasswordHash,
                                   StaffPin = queryForAuthentication.ToList()[0].StaffPin;
                            userpassWordHash = Common.GetPasswordHash(AdminLoginInput.Password, DBPasswordSalt);
                            if ((DBPasswordHash == userpassWordHash) && (AdminLoginInput.StaffPin.ToLower() == StaffPin.ToLower()))
                            {
                                checkPassword = true;
                                CheckStaffPin = true;
                            }
                            else if (DBPasswordHash == userpassWordHash)
                            {
                                checkPassword = true;
                            }
                            else if (AdminLoginInput.StaffPin.ToLower() == StaffPin.ToLower())
                            {
                                CheckStaffPin = true;
                            }


                        }

                    }

So in my code i have created object of my db context and then i have a query 'queryForAuthentication' and in that i have used two tables 'conDb.SystemMasters' and joined with conDb.SystemAdminMasters , so will hit twice or how does it manage . i want to know when does entity framework will hit in to database ?

No it doesn't hit twice. It build a query that joins the tables and runs that query once. Then it does 'lazy loading' which means that it doesn't actually run the query until you actually try and refer to data in it. So if you use `ToList()` or try to get a value out of a column, thats when it runs. — Nick.Mc, Jul 10 '17 at 05:30
This shows you how to inspect the SQL that it builds: https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework Again: this SQL does not run until you try to refer to data — Nick.Mc, Jul 10 '17 at 05:31
@Nick.McDermaid Bro check my updated code, i have added the actual use of that query . That is how im exploring that query and using data . So after that i,m checking the count and then using linq query to check if the user exist , is that time it hits the Database ? — Nik, Jul 10 '17 at 05:44
Yes - in your sample code, it doesn't run the database query until it hits `.Count()`. And it (usually) only runs one query against the database which has been efficiently built by LINQ to join the two tables in the actual query. This is in your particular case where you are reading a database. There are many other cases for LINQ where you aren't running against a database where things work a bit diferently. — Nick.Mc, Jul 10 '17 at 05:48
@Nik IMHO it will hit twice at the database `queryForAuthentication.Count()` and other `queryForAuthentication.ToList()` — Eldho, Jul 10 '17 at 05:59
@Eldho It will hit twice ? You mean in my code : queryForAuthentication.Count() 'string DBPasswordSalt = queryForAuthentication.ToList()[0].PasswordSalt, DBPasswordHash = queryForAuthentication.ToList()[0].PasswordHash', will hit trice ? — Nik, Jul 10 '17 at 06:35
As you have interested in the items I would prefer first invoke `var result = queryForAuthentication.ToList()` after that you can simple use `result.Count` . Please verify using profiler to make sure you don't hit database twice — Eldho, Jul 10 '17 at 06:43
@Eldho thanks for all your help. You helped great I got it now . ! So basically i have to avoid performing from main Linq query as much i can . And declare a seperate variable and then perform operations on that , but not the 'queryForAuthentication' — Nik, Jul 10 '17 at 06:54
@Eldho, actually you're probably right but the only way to know for sure is profile the DB. I had assumed he meant twice for two tables but indeed it will probably do a count then select all the data. Which is of course redundant - you can just check if the object is empty right? — Nick.Mc, Jul 11 '17 at 04:34
Operators like ToList, ToDictionary and ToLookup are all greedy operators that will execute a query immediately and construct an in-memory data structure. — VAT, Aug 30 '19 at 12:54

score 4 · Accepted Answer · answered Jul 10 '17 at 06:48

4

It's hits the database whenever you fire a query. And query will be fired whenever you perform ToList, First, FirstOrDefault etc. operation. Till then it only builds the query.

answered Jul 10 '17 at 06:48

tinkb

56
2

kari kalan · Answer 2 · 2017-07-10T07:29:21.707

try Code

 QuizzrEntities conDb = new QuizzrEntities();
 List<OnLoginData> lstOnLogoonData = new List<OnLoginData>();
 string userpassWordHash = string.Empty;
 var queryForAuthentication =(from systemObj in conDb.SystemMasters
                         where systemObj.StaffPin == dminLoginInput.StaffPin
                         join admin in conDb.SystemAdminMasters on systemObj.SystemId equals admin.SystemID
                         select new 
                        {
                        PasswordSalt= admin.PasswordSalt,
                        PasswordHash= admin.PasswordHash, 
                        StaffPin= systemObj.StaffPin,
                        UserName= admin.UserName, 
                       SystemID =  admin.SystemID 
                        }).FirstOrDefault();
If(queryForAuthentication !=null)
{
-----------------
-----------------
*****Your Code*******
}

To expand on this: this code does not use `.Count()` so it _probably_ does not run a seperate count against the DB... but we can't be sure without testing. It might count the records locally — Nick.Mc, Jul 11 '17 at 04:35

score 0 · Answer 3 · answered Jul 10 '17 at 05:36

In entity framework also work with sql query based. If you are disconnected using .ToList() then only the record taken from local otherwise it's works as DBQuery. if you check the result view in debug view it's Execute the Query and Return the data.

If you are processing the data is discontinued from the base it's executed finally where you want the result.

You processing data locally then you can disconnect the connection between linq and sql using call .ToList(). it's Processing only one time the Object weight is high more than query.

score 0 · Answer 4 · answered Jul 10 '17 at 06:58

var queryForAuthentication =from systemObj in conDb.SystemMasters
                         where systemObj.StaffPin == dminLoginInput.StaffPin
                         join admin in conDb.SystemAdminMasters on systemObj.SystemId equals admin.SystemID
                         select new 
                        {
                         admin.PasswordSalt,
                         admin.PasswordHash, 
                         systemObj.StaffPin,
                         admin.UserName, 
                         admin.SystemID 
                        }.ToList() ; // It will fetch the data
//Check from inmemory collection 
if (queryForAuthentication.Count > 0)

//As you already have the data in memory this filter applied against inmemory collection not against database.
var GetUserUsingUsernamePasword = queryForAuthentication
             .Where(u =>u.UserName.ToLower() == AdminLoginInput.UserName.ToLower());

Okay thank you so much alot to learn from you . Connecting on social on facebook is that fine with you ? @Eldho — Nik, Jul 10 '17 at 07:02

When does Entity framework Linq query Hits Sql Databse?

4 Answers4