Hi,
Run into a strange problem while writing an ASP.NET MVC site. I have a view in my SQL Server database that returns a few date ranges. The view works fine when running the query in SSMS.
When the view data is returned by the Entity Framework Model, It returns the correct number of rows but some of the rows are duplicated.
Here is an example of what I have done:
SQL Server code:
CREATE TABLE [dbo].[A](
[ID] [int] NOT NULL,
[PhID] [int] NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
([ID] ASC)) ON [PRIMARY]
go
CREATE TABLE [dbo].[B](
[PhID] [int] NOT NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
( [PhID] ASC )) ON [PRIMARY]
go
CREATE VIEW C as
SELECT A.ID,
CASE WHEN A.PhID IS NULL THEN A.FromDate ELSE B.FromDate END AS FromDate,
CASE WHEN A.PhID IS NULL THEN A.ToDate ELSE B.ToDate END AS ToDate
FROM A
LEFT OUTER JOIN B ON A.PhID = B.PhID
go
INSERT INTO B (PhID, FromDate, ToDate) VALUES (100, '20100615', '20100715')
INSERT INTO A (ID, PhID, FromDate, ToDate) VALUES (1, NULL, '20100101', '20100201')
INSERT INTO A (ID, PhID, FromDate, ToDate) VALUES (1, 100, '20100615', '20100715')
INSERT INTO B (PhID, FromDate, ToDate) VALUES (101, '20101201', '20101231')
INSERT INTO A (ID, PhID, FromDate, ToDate) VALUES (2, NULL, '20100801', '20100901')
INSERT INTO A (ID, PhID, FromDate, ToDate) VALUES (2, 101, '20101201', '20101231')
So now, if you select all from C, you get 4 separate date ranges
In the Entity Framework Model (which I call 'Core'), the view 'C' is added.
in MVC Controller:
public class HomeController : Controller
{
public ActionResult Index()
{
CoreEntities db = new CoreEntities();
var clist = from c in db.C
select c;
return View(clist.ToList());
}
}
in MVC View:
@model List<RM.Models.C>
@{
foreach (RM.Models.C c in Model)
{
@String.Format("{0:dd-MMM-yyyy}", c.FromDate)
<span>-</span>
@String.Format("{0:dd-MMM-yyyy}", c.ToDate)
<br />
}
}
When I run all this, it outputs this:
01-Jan-2010 - 01-Feb-2010
01-Jan-2010 - 01-Feb-2010
01-Aug-2010 - 01-Sep-2010
01-Aug-2010 - 01-Sep-2010
When it should do this (this is what the view returns):
01-Jan-2010 - 01-Feb-2010
15-Jun-2010 - 15-Jul-2010
01-Aug-2010 - 01-Sep-2010
01-Dec-2010 - 31-Dec-2010
Also, I've run the SQL profiler over it and according to that, the query being executed is:
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[FromDate] AS [FromDate],
[Extent1].[ToDate] AS [ToDate]
FROM (SELECT
[C].[ID] AS [ID],
[C].[FromDate] AS [FromDate],
[C].[ToDate] AS [ToDate]
FROM [dbo].[C] AS [C]) AS [Extent1]
Which returns the correct data
So it seems that the entity framework is doing something to the data in the meantime.
To me, everything looks fine! Have I missed something?
Cheers,
Ben
