Why VIEW data don't show correctly in gridview ?

Question

0.00/5 (No votes)

See more:

, +

hi,
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();
          }

Posted 3-Apr-19 4:54am

Golden Basim

Updated 4-Apr-19 4:08am

v2

Add a Solution

Comments

[no name] 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

2 solutions

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?

Posted 3-Apr-19 8:38am

#realJSOP

Updated 3-Apr-19 8:39am

v2

Comments

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)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2019-04-04T04:09:00

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[^]