Click here to Skip to main content
14,207,436 members
Rate this:
Please Sign up or sign in to vote.
i have MySQL VIEW :
(4 record)

sDate , Count , Amount
2019-04-01, 2, 600.0000
2019-04-02, 1, 4000.0000
2019-04-03, 2, 1400.0000
2019-04-04, 1, 3500.0000

i try to show this data in Gridview using EF6
var Purchases = DB1.view_purchases_amount_per_day.Where(x => x.sDate >= FirstDayOfMonth &&
                                       x.sDate <= LastDayOfMonth);

view_purchases_amount_per_dayBindingSource.DataSource =  Purchases.ToList();

but only the first and second record winch appear (twice) :
sDate , Count , Amount
2019-04-01, 2, 600.0000
2019-04-02, 1, 4000.0000
2019-04-01, 2, 600.0000
2019-04-02, 1, 4000.0000

What I have tried:

i tried to manully check the result but same problem:
foreach(var  p in Purchases.ToList())
    string aaa = p.sDate.ToString();
   string aaa11 = p.Count.ToString();
   string aaa22 = p.Amount.ToString();

i did a foreach without .Where() and the result have the same problem
var Purchases1 = DB1.view_purchases_amount_per_day;
          foreach(var p in Purchases1)
              string date = p.sDate.ToString();
              string count = p.Count.ToString();
              string amount = p.Amount.ToString();
Updated 4-Apr-19 4:08am
Gerry Schmitz 3-Apr-19 13:38pm
Are you blaming the grid or the query?
Golden Basim 3-Apr-19 13:45pm
the problem with the query ..i checked the query in foreach to see the result for every record ..
#realJSOP 3-Apr-19 14:12pm
Did you verify that the data in your database isn't somehow duplicated?
Golden Basim 3-Apr-19 14:14pm
yes i'm sure
#realJSOP 3-Apr-19 14:30pm
And you verified that you're getting the expected data without dupes in your dataset?
Golden Basim 3-Apr-19 14:57pm
the data is correct i checked it using workbench
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

It looks like you've created an entity type to represent the values returned from your view, and you've set the Count column as the primary key for that type.

EF uses the Identity map pattern[^]. When it tries to load the third row (Count = 2), it thinks that it has already loaded that record. It ignores the data from the database, and returns the already-loaded entity instead.

You need to change the entity type to have a composite primary key based on sDate and Count instead. And if that combination of columns isn't unique, then you'll need to add a new unique column to your view, and set that as the primary key on your entity type.

sql - Using a view with no primary key with Entity - Stack Overflow[^]
Golden Basim 4-Apr-19 10:40am
it work thank you
Maciej Los 4-Apr-19 12:52pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

0) Your linq query makes no sense because EVERY day is between the first/last day of its month. Maybe you meant to compare just the month? (We can't see what the vars LastDayofMonth and FirstDayOfMonth are, but I assumed they're a datetime.) Maybe better names would be StartDate and EndDate?

1) If you did a foreach on your list and got the same results, the data is somehow being duplicated BEFORE you get to your .Where() clause .

2) You do know you can append .ToList() to your .Where() clause and save yourself as tep. Right?
Golden Basim 4-Apr-19 4:49am
0) i checked that during debugging
 FirstDayOfMonth =  {01/04/2019 12:00:00 ص} LastDayofMonth = {30/04/2019 12:00:00 ص} <pre>
1) Yes, i did a foreach BEFORE i get to .Where(), and the data have the same problem.( it repeat the first and second record twice , I have 4 record in the VIEW)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190612.1 | Last Updated 4 Apr 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100