locked
ForEach loop on View not iterating? RRS feed

  • Question

  • I am trying to spin through a view using a for each loop as follows:

    List<object> orders = new List<object>();
    foreach (vw_UniqueOrderYear viewItem in new DataWorkspace().PricingView.vw_UniqueOrderYears)
    {
       orders.Add(viewItem.OrderDate);
    }


    and each iteration returns the same record?

    Here is my view definition:

    CREATE VIEW [dbo].[vw_UniqueOrderYears]
    	AS SELECT DiSTINCT(YEAR([Orders].[OrderDate])) as [OrderDate], ISNULL(GETDATE(), 0) as [Now]
    	 FROM [Orders]

    Any ideas?

    Thanks

    Monday, October 21, 2013 7:52 PM

Answers

  • I am going to keep this post up here because it may help somebody else (although that might be unlikely). The reason this was happening is because of the derived  "[Now]" field in the view. That particular value will always evaluate to the same thing (the exact date/time to the millisecond). In order to get Lightswitch to work with views that don't reference a primary key field in some way, I used that field as my primary key (by using the ISNULL function), and when the iterator in my code was trying to iterate over the primary key, the value returned was always the same thing. 

    I changed the view to look like this and it worked:

    CREATE VIEW [dbo].[vw_YearsAvailibleToReportOnSessions]
    	AS SELECT DISTINCT(ISNULL(YEAR(SESSIONDATE), 1990)) as [Year] 
    	FROM [dbo].[Sessions]

    I used the ISNULL function on the value I cared about.

    Hope this helps somebody!

    Mitch


    Tuesday, October 22, 2013 3:30 PM