Why this query produce wrong results ?
-
Hello ! I'm using Entity Framework6 with VB.net and SQL server 2008R2. I have this case : This is my model :
Partial Public Class Myobj
Public Property id As Integer
Public property name as string
Public Overridable Property chld As ICollection(Of chld) = New HashSet(Of chld)
End ClassPartial Public Class Myobj
Public shared cond1 as DateTime
<NotMapped> Public ReadOnly Property vls As integer
Get
Return chld.AsQueryable.Where(Function(t2) t2.date1<cond1).Select(Function(t3) t3.quantity).DefaultIfEmpty.Sum()
End Get
End Property
End ClassPartial Public Class chld
Public Property id As Integer
Public Property date1 as DateTime
Public Property quantity as Integer
Public Property ParentID as integer
Public Overridable Property MyObj1 As MyObj
End ClassNow on my form, I have this code :
Dim dt1 as DateTime=CDate("08/08/2014")
Myobj.cond1=dt1Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _
.chl = (From t2 In t.chld.AsQueryable.Where(Function(t3) t3.Date1>=dt1) Select t2)
}).ToListThis query always produces 0 in vls property for each item in Myobj1.( !! but it's not true according to data in database!!). Why this query product a such result? Thank you!
-
Hello ! I'm using Entity Framework6 with VB.net and SQL server 2008R2. I have this case : This is my model :
Partial Public Class Myobj
Public Property id As Integer
Public property name as string
Public Overridable Property chld As ICollection(Of chld) = New HashSet(Of chld)
End ClassPartial Public Class Myobj
Public shared cond1 as DateTime
<NotMapped> Public ReadOnly Property vls As integer
Get
Return chld.AsQueryable.Where(Function(t2) t2.date1<cond1).Select(Function(t3) t3.quantity).DefaultIfEmpty.Sum()
End Get
End Property
End ClassPartial Public Class chld
Public Property id As Integer
Public Property date1 as DateTime
Public Property quantity as Integer
Public Property ParentID as integer
Public Overridable Property MyObj1 As MyObj
End ClassNow on my form, I have this code :
Dim dt1 as DateTime=CDate("08/08/2014")
Myobj.cond1=dt1Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _
.chl = (From t2 In t.chld.AsQueryable.Where(Function(t3) t3.Date1>=dt1) Select t2)
}).ToListThis query always produces 0 in vls property for each item in Myobj1.( !! but it's not true according to data in database!!). Why this query product a such result? Thank you!
You have to
.Include(_related_)the related tables if you want the related objects rehydrated in EF. Discussed here[^]A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
You have to
.Include(_related_)the related tables if you want the related objects rehydrated in EF. Discussed here[^]A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakSorry , but what I should to include ? If you can see , each Myobj1 object has a Calculated property vls that have a query that calculate the sum Why should I include these ? I don't want to read from database the child's items before the date 08/08/2014 , but I want to get only the quantity's sum. and if the query inside the partial class will be a normal query outside the class , will be work as expected without including nothing.
-
Sorry , but what I should to include ? If you can see , each Myobj1 object has a Calculated property vls that have a query that calculate the sum Why should I include these ? I don't want to read from database the child's items before the date 08/08/2014 , but I want to get only the quantity's sum. and if the query inside the partial class will be a normal query outside the class , will be work as expected without including nothing.
It's because all of your child objects are null. They don't get re-hydrated unless they are listed in an Include on the original query. That's why you're getting 0's in the calculated field. There is nothing to run any calculations on!
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
It's because all of your child objects are null. They don't get re-hydrated unless they are listed in an Include on the original query. That's why you're getting 0's in the calculated field. There is nothing to run any calculations on!
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakIn my main query , the first part is :
Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _In this moment , one of the property in"t" is vls. And this property get the value form that query inside the partial class. This query doesn't get executed ? and why this is working ok :
Dim dt1 as DateTime=CDate("08/08/2014")
Myobj.cond1=dt1Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _
.chl = (From t2 In t.chld.AsQueryable.Where(Function(t3) t3.Date1>=dt1) Select t2)
.sum1 =(From t2 in t.chld.AsQueryable.Where(Function(t2) t2.date1In this case the sum1 results are ok ( as you can see it's the same query as inside the partial class )
The vls property inside the partial class have the same query. -
In my main query , the first part is :
Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _In this moment , one of the property in"t" is vls. And this property get the value form that query inside the partial class. This query doesn't get executed ? and why this is working ok :
Dim dt1 as DateTime=CDate("08/08/2014")
Myobj.cond1=dt1Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _
.chl = (From t2 In t.chld.AsQueryable.Where(Function(t3) t3.Date1>=dt1) Select t2)
.sum1 =(From t2 in t.chld.AsQueryable.Where(Function(t2) t2.date1In this case the sum1 results are ok ( as you can see it's the same query as inside the partial class )
The vls property inside the partial class have the same query.The concept is called "deferred execution". Your query isn't actually run until the code gets to the call to .ToList(). The reason why the
vlsproperty is 0 is because your property getter is not part of the query that re-hydrates the objects. That code isn't run until you call the property from some other code. I already told you why that property is returning 0. Because the objects it's working with don't exist yet. Your original query never re-hydrated the chld objects, so the vls property didn't have anything to work with. That's why you need the .Includes(). It works if the code is part of the query because it gets baked into the SQL that is sent to the database. Again, you can SEE THIS by following the technique I told you about before. Get the SQL SELECT statement and paste it into a query window or into Notepad and READ IT. Do you actually read any of the links we post? They would help you to understand what's going on in EF because you don't seem to have a grasp of it yet. Seriously, before you waste a ton of time whould why things are working, pickup the Programming Entity Framework and/or the Code First books by Julie Lerman and read them.A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
The concept is called "deferred execution". Your query isn't actually run until the code gets to the call to .ToList(). The reason why the
vlsproperty is 0 is because your property getter is not part of the query that re-hydrates the objects. That code isn't run until you call the property from some other code. I already told you why that property is returning 0. Because the objects it's working with don't exist yet. Your original query never re-hydrated the chld objects, so the vls property didn't have anything to work with. That's why you need the .Includes(). It works if the code is part of the query because it gets baked into the SQL that is sent to the database. Again, you can SEE THIS by following the technique I told you about before. Get the SQL SELECT statement and paste it into a query window or into Notepad and READ IT. Do you actually read any of the links we post? They would help you to understand what's going on in EF because you don't seem to have a grasp of it yet. Seriously, before you waste a ton of time whould why things are working, pickup the Programming Entity Framework and/or the Code First books by Julie Lerman and read them.A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakThank you ! But can you get me a suggestion how resolve the situation ? Is there any method to put those sum in the Calculated property ? Actually , I try to run like this :
Dim dt1 as DateTime=CDate("08/08/2014")
Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _
.chl = (From t2 In t.chld.AsQueryable.Where(Function(t3) t3.Date1>=dt1) Select t2)
.sum1 =(From t2 in t.chld.AsQueryable.Where(Function(t2) t2.date1<dt1).Select(Function(t3) t3.quantity).DefaultIfEmpty
}).ToListand after :
For Each l In list1
l.Parent.vls=l.sum1
NextBut this is executed very slow. Is there any better method ? Thank you !
-
Thank you ! But can you get me a suggestion how resolve the situation ? Is there any method to put those sum in the Calculated property ? Actually , I try to run like this :
Dim dt1 as DateTime=CDate("08/08/2014")
Dim list1 = (From t In context.MyObj Select New With { _
.Parent = t, _
.chl = (From t2 In t.chld.AsQueryable.Where(Function(t3) t3.Date1>=dt1) Select t2)
.sum1 =(From t2 in t.chld.AsQueryable.Where(Function(t2) t2.date1<dt1).Select(Function(t3) t3.quantity).DefaultIfEmpty
}).ToListand after :
For Each l In list1
l.Parent.vls=l.sum1
NextBut this is executed very slow. Is there any better method ? Thank you !
I already told you what to do. I gave you a link that shows you how to do it. I've even told you why your code doesn't work.
Dim list1 = (From t in context.MyObject**_.Include("chld")_** Select New ...A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
I already told you what to do. I gave you a link that shows you how to do it. I've even told you why your code doesn't work.
Dim list1 = (From t in context.MyObject**_.Include("chld")_** Select New ...A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakYes , but I think you don't understand that I don't want to include all the childs. For the childs with date before 08/08/2014 I want to get only the quantity's sum. The childs with date after 08/08/2014 I want to include. The code that I write in my last post , do what I want , but the part " From each...... run very slow. Do you know a better solution ?
-
Yes , but I think you don't understand that I don't want to include all the childs. For the childs with date before 08/08/2014 I want to get only the quantity's sum. The childs with date after 08/08/2014 I want to include. The code that I write in my last post , do what I want , but the part " From each...... run very slow. Do you know a better solution ?
You have a choice. You can either 1) The slow way. Do the calculation in your property getter where you have to wait for EF to re-hydrate all the related child objects into memory and then your getter code applies its filter and does the calculation. Or 2) You drop the property getter code and move the filtering to the query and let the database filter the child objects and do the math for you. Something the database engine was designed to do very well. The choice is up to you.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
You have a choice. You can either 1) The slow way. Do the calculation in your property getter where you have to wait for EF to re-hydrate all the related child objects into memory and then your getter code applies its filter and does the calculation. Or 2) You drop the property getter code and move the filtering to the query and let the database filter the child objects and do the math for you. Something the database engine was designed to do very well. The choice is up to you.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
Thank you !. The problem is that for some reasons I need to have the sum values in a Myobj's property. Do you think that is possible that I can realize your second variant with my above condition ? Thank you !
Ho many minutes would it take you to TRY IT?
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
Ho many minutes would it take you to TRY IT?
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
I ALREADY HAVE! MULTIPLE TIMES! Go back and re-read these posts!
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
I ALREADY HAVE! MULTIPLE TIMES! Go back and re-read these posts!
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakQuote:
You have a choice. You can either 1) The slow way. Do the calculation in your property getter where you have to wait for EF to re-hydrate all the related child objects into memory and then your getter code applies its filter and does the calculation. Or 2) You drop the property getter code and move the filtering to the query and let the database filter the child objects and do the math for you. Something the database engine was designed to do very well.
Can you explain how to do the second ?
-
Quote:
You have a choice. You can either 1) The slow way. Do the calculation in your property getter where you have to wait for EF to re-hydrate all the related child objects into memory and then your getter code applies its filter and does the calculation. Or 2) You drop the property getter code and move the filtering to the query and let the database filter the child objects and do the math for you. Something the database engine was designed to do very well.
Can you explain how to do the second ?
No, because YOU already did it --> http://www.codeproject.com/Messages/5006723/Re-Why-this-query-produce-wrong-results.aspx[^]
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
No, because YOU already did it --> http://www.codeproject.com/Messages/5006723/Re-Why-this-query-produce-wrong-results.aspx[^]
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
yes , but after these instructions , I need to put those sums in the .vls . and to do this I know only this way :
For Each l In list1
l.Parent.vls=l.sum1
NextBut as I wrote in one of my previous messages this is too slow
I told you you can't do what you want to do and get the speed out of it. It just isn't possible.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
I told you you can't do what you want to do and get the speed out of it. It just isn't possible.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakQuote:
You have a choice. You can either 1) The slow way. Do the calculation in your property getter where you have to wait for EF to re-hydrate all the related child objects into memory and then your getter code applies its filter and does the calculation. Or 2) You drop the property getter code and move the filtering to the query and let the database filter the child objects and do the math for you. Something the database engine was designed to do very well. The choice is up to you.
Yes but you call the first way as "slow way" an not the second. Why the second is slow ?
-
Quote:
You have a choice. You can either 1) The slow way. Do the calculation in your property getter where you have to wait for EF to re-hydrate all the related child objects into memory and then your getter code applies its filter and does the calculation. Or 2) You drop the property getter code and move the filtering to the query and let the database filter the child objects and do the math for you. Something the database engine was designed to do very well. The choice is up to you.
Yes but you call the first way as "slow way" an not the second. Why the second is slow ?
Dude. I don't have your database. I don't know what your overall objective is. I don't know what you're reasons are for anything and you've put up so many code snippets, I have no idea what you're referring to or what you're referring to when you say "slow". Without that stuff, I'm at a severe disadvantage in diagnosing your problems. YOU have to understand how EF works and you're not going to get that information from a few forum posts. I already told you, pick up a book on EF and Code First and work through them. I've already told you why your queries are returning 0's for values, but you don't seems to get it and you don't seem to understand the limitations of your design or of EF.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak