为什么VIEW数据无法在gridview中正确显示？ [英] Why VIEW data don't show correctly in gridview ?

问题描述

i有MySQL视图：

（4条记录）



sDate，Count，Amount

2019-04-01,2,600.0000

2019-04-02,1,40000.0000

2019-04-03,2， 1400.0000

2019-04-04,1,335.0000





i尝试使用Gridview显示此数据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（）; 

但是只有第一个和第二个记录绞盘出现（两次）：

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,40000.0000



我尝试了什么：



i试图仔细检查结果但同样的问题：

 foreach（在Purchases.ToList（）中的var p）
 {
 string aaa = p.sDate.ToString（）; 
 string aaa11 = p.Count.ToString（）; 
 string aaa22 = p.Amount.ToString（）; 
} 

i做了一个foreach而没有.Where （）并且结果具有相同的问题

 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（）; 
} 
 

解决方案

看起来您已经创建了一个实体类型来表示从您返回的值查看，并将 Count 列设置为该类型的主键。



EF使用身份地图模式 [ ^ ]。当它尝试加载第三行（Count = 2）时，它认为它已经加载了该记录。它忽略了数据库中的数据，并返回已经加载的实体。



您需要更改实体类型以获得基于<$的复合主键c $ c> sDate 和计数。如果列的组合不是唯一的，那么您需要在视图中添加一个新的唯一列，并将其设置为实体类型的主键。



sql - 使用没有主键的视图实体 - 堆栈溢出 [ ^ ]

0）你的linq查询毫无意义，因为每一天都在它的月的第一天/最后一天之间。也许你想比较一个月？ （我们无法看到vars LastDayofMonth 和 FirstDayOfMonth 是什么，但我认为它们是日期时间。）也许更好的名字是 StartDate 和 EndDate ？



1）如果您在列表中执行了 foreach 并获得了相同的结果，那么在您到达之前，数据会以某种方式被复制。 ）条款。



2）你知道你可以附加 .ToList（）到您的 .Where（）子句并将自己保存为tep。右

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

解决方案

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

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?

这篇关于为什么VIEW数据无法在gridview中正确显示？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！