Doug Schmidt
asked on
Entity Framework select statement is selecting the same row4 times instead of 4 different rows and I can't figure out why
I am using entity framework to fill a nested repeater from a SQL View. It should be selecting the 4 highlighted rows in the view output below:
When I look at the results in debugger it is showing the 1st record, "Body Weight Strength", 4 times. All fields in each row are identical including the time stamp. Of course the repeater is loading the "Body Weight -Strength" row 4 times instead of the four individual rows.
Code from aspx page:
The c# code:
The view code:
The page in browser:
I can't see what I'm doing wrong. Any help would be appreciated.
Oh - by the way one of the variables is only accessible in the repeater so I don't think I can access it from a sqldatatsource control.
Thanks
Doug
When I look at the results in debugger it is showing the 1st record, "Body Weight Strength", 4 times. All fields in each row are identical including the time stamp. Of course the repeater is loading the "Body Weight -Strength" row 4 times instead of the four individual rows.
Code from aspx page:
<asp:Repeater ID="rptSummary" runat="server" DataSourceID="sdsSummary">
<HeaderTemplate>
<table>
<tr>
<td></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr class="Bold">
<td>
<asp:Label ID="lblWorkoutName" runat="server" Text='<%# Eval ("[Work Out Name]") %>'></asp:Label>
<asp:HiddenField ID="hdnWorkOutHistoryId" runat="server" Value='<%# Eval("intWorkOutHistoryID") %>' />
</td>
<td class="Center" style="width: 250px">
<asp:Label ID="lblDate" runat="server" Text='<%# Eval ("dtmDateStarted","{0:ddd, MMM d yyyy}") %>'></asp:Label>
</td>
<td class="Right" style="width: 135px">
<asp:Label ID="lblStartTime" runat="server" Text='<%# Eval ("dtmDateStarted","{0:h:mm tt}") %>'></asp:Label>
</td>
</tr>
<tr>
<td colspan="2" class="Right">Work Out Duration:</td>
<td class="Right">
<asp:Label ID="lblTotalTime" runat="server" Text='<%# (Convert.ToDateTime(Eval("dtmDateEnded")) - Convert.ToDateTime(Eval("dtmDateStarted"))).Duration().ToString("h\\:mm\\:ss") %>'></asp:Label>
</td>
</tr>
<tr>
<td></td>
<td colspan="2">
<asp:Repeater ID="rptSummaryDetails" runat="server">
<HeaderTemplate>
<table>
<tr>
<th></th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<asp:Label ID="lblGoalType" runat="server" Text='<%# Eval("strExerciseType") %>'></asp:Label>
</td>
<td>
<asp:Label ID="lblGoal" runat="server" Text='<%# Eval("decTotal") %>'></asp:Label>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
<tr><td></td></tr>
</table>
</FooterTemplate>
</asp:Repeater>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnWorkOutDetails" runat="server" Text="Work Out Details" />
</td>
</tr>
<tr><td> </td></tr>
</ItemTemplate>
<FooterTemplate>
<tr>
<td></td>
</tr>
</table>
</FooterTemplate>
</asp:Repeater>The c# code:
protected override void OnPreRenderComplete(EventArgs e)
{
base.OnPreRenderComplete(e);
int intWorkOutHistoryID;
string strExerciseType;
decimal decTotal;
//build data table
DataTable dtSummaryDetails = new DataTable();
dtSummaryDetails.Columns.Add("strExerciseType", typeof(string));
dtSummaryDetails.Columns.Add("decTotal", typeof(decimal));
foreach (RepeaterItem item in rptSummary.Items)
{
HiddenField hdnWorkOutHistoryId = (HiddenField)item.FindControl("hdnWorkOutHistoryId"); //find control holding info
intWorkOutHistoryID = Convert.ToInt32(hdnWorkOutHistoryId.Value); //fetch data from page
using (var myEntities = new dbWorkOutEntities())
{
var ExerciseTypes = (from type in myEntities.VWorkOutHistoryExerciseTypes //look up exercise types
where type.intPersonID == f_intPersonId
&& type.intWorkOutID == f_intWorkOutId
&& type.intWorkOutHistoryID == intWorkOutHistoryID
select type);
if (ExerciseTypes != null)
{
foreach (var type in ExerciseTypes)
{
strExerciseType = type.strExcersizeType;
decTotal = 10;
//calculate total
//add row to data table
dtSummaryDetails.Rows.Add(new object[] { strExerciseType, decTotal });
}
}
}
Repeater rptSummaryDetails = (Repeater)item.FindControl("rptSummaryDetails"); //find detail repeater
//bind detail repeater
rptSummaryDetails.DataSource = dtSummaryDetails;
rptSummaryDetails.DataBind();
}
}The view code:
GO
CREATE VIEW VWorkOutHistoryExerciseTypes
AS
SELECT
TP.intPersonID
,TP.strFirstName + ' ' + TP.strLastName AS Name
,TPWO.intWorkOutID
,TPWO.strWorkOutName
,TPWOHD.intWorkOutHistoryID
,TET.intExcersizeTypeID
,TET.strExcersizeType
,MIN(TPWOHD.dtmDatePerformed) AS Date
FROM
tPeopleWorkOutHistoryDetail as TPWOHD
INNER JOIN tPeople as TP
ON TPWOHD.intPersonID = TP.intPersonID
INNER JOIN tPeopleWorkOuts AS TPWO
ON TPWOHD.intWorkOutID = TPWO.intWorkOutID
LEFT OUTER JOIN tExcersizeTypes AS TET
ON TPWOHD.intExcersizeTypeID = TET.intExcersizeTypeID
GROUP BY
TP.intPersonID
,TP.strFirstName + ' ' + TP.strLastName
,TPWO.intWorkOutID
,TPWO.strWorkOutName
,TPWOHD.intWorkOutHistoryID
,TET.intExcersizeTypeID
,TET.strExcersizeType
GOThe page in browser:
I can't see what I'm doing wrong. Any help would be appreciated.
Oh - by the way one of the variables is only accessible in the repeater so I don't think I can access it from a sqldatatsource control.
Thanks
Doug
zephyr_hex (Megan)
Does ExerciseTypes on line 21 of your C# code contain the results you expect?
ASKER
No - that's where I think the problem lies but I just don't see what the problem is
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Megan,
All three variables are what I expect - double and triple checked. The .Equals() syntax produces the same erroneous results. I ran the same query in SQL Server Management Suite, translated to TSQL with the variable values types in, and it produced the expected correct results.
I took the variable values out of debugger just to make sure.
Any ideas on where to go from here?
Doug
All three variables are what I expect - double and triple checked. The .Equals() syntax produces the same erroneous results. I ran the same query in SQL Server Management Suite, translated to TSQL with the variable values types in, and it produced the expected correct results.
I took the variable values out of debugger just to make sure.
Any ideas on where to go from here?
Doug
Hmmmm...
Maybe try a List , and then inspect the results in debugger ? This is a long stab in the dark because I'm not really sure what would cause the behavior you're experiencing.
Or try :
Maybe try a List , and then inspect the results in debugger ? This is a long stab in the dark because I'm not really sure what would cause the behavior you're experiencing.
var test = (from type in myEntities.VWorkOutHistoryExerciseTypes
where type.intPersonID.Equals(f_intPersonId)
&& type.intWorkOutID.Equals(f_intWorkOutId)
&& type.intWorkOutHistoryID.Equals(intWorkOutHistoryID)
select type).ToList();Or try :
var test = myEntities.VWorkOutHistoryExerciseTypes.Where(w => w.intPersonID == f_intPersonId && w.intWorkOutID == f_intWorkOutId && w.intWorkOutHistoryID == intWorkOutHistoryID).ToList();