none
How to get property (with LINQ) of previous item and next item in table? RRS feed

  • Question

  • How to get property (with LINQ) of previous item and next item in table than item which are selected with LINQ statement? For example, how to do that previousPhotoID equals PhotoID of item which is before item with PhotoID.ToString() == Request.QueryString["photo"] and nextPhotoID equals PhotoID of item which is after item with PhotoID.ToString() == Request.QueryString["photo"]:

    Code Snippet

    var photo = from i in db.Photos

    where i.PhotoID.ToString() == Request.QueryString["photo"]

    select new { i.PhotoID, i.PhotoTitleEN, i.PhotoTitlePL, i.PhotoFileName, i.PhotoDescriptionEN, i.PhotoDescriptionPL };

     previousPhotoID =

    thisPhotoFileName = photo.Single().PhotoFileName;

    nextPhotoID =

     

     

    Items in this table do not have all numbers in PhotoID in turn, eg. after item with PhotoID = 2, there is item with PhotoID = 4 (item with PhotoID = 3 does not exist). How to do this? Could you help me?

    Thursday, August 7, 2008 12:52 PM

Answers

  • The result are ordered by SQL. My problem was how to get previous and next item. Items in this table do not have all numbers in PhotoID in turn, eg. after item with PhotoID = 2, there is item with PhotoID = 4 (item with PhotoID = 3 does not exist) so I can't increase and decrease PhotoID.

     

    I do this in this way:

     

    Code Snippet

    var previous = from pi in db.Photos

    where pi.PhotoID < photo.Single().PhotoID

    select new { pi.PhotoID };

    var next = from ni in db.Photos

    where ni.PhotoID > photo.Single().PhotoID

    select new { ni.PhotoID };

    if (previous.Count() == 0)

    {

    PreviousAreaEnd.Text = "' coords='0,4,24,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + next.Max(ni => ni.PhotoID).ToString() + "' />";

    NextAreaEnd.Text = "' coords='54,4,78,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + next.Min(ni => ni.PhotoID).ToString() + "' />";

    }

    else

    {

    PreviousAreaEnd.Text = "' coords='0,4,24,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + previous.Max(pi => pi.PhotoID).ToString() + "' />";

    if (next.Count() == 0)

    {

    NextAreaEnd.Text = "' coords='54,4,78,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + previous.Min(pi => pi.PhotoID).ToString() + "' />";

    }

    else

    {

    NextAreaEnd.Text = "' coords='54,4,78,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + next.Min(ni => ni.PhotoID).ToString() + "' />";

    }

    }

     

     

    Thursday, August 7, 2008 6:14 PM

All replies

  • The order in which the database (and by extension, LINQ to SQL) returns results is undefined, unless you explicitly add an order-by clause.

     

    You can add an "orderby PhotoID" clause to your query and get the results in the desired sequence.

     

    Thanks,

     

    --Samir

     

    Thursday, August 7, 2008 4:44 PM
  • The result are ordered by SQL. My problem was how to get previous and next item. Items in this table do not have all numbers in PhotoID in turn, eg. after item with PhotoID = 2, there is item with PhotoID = 4 (item with PhotoID = 3 does not exist) so I can't increase and decrease PhotoID.

     

    I do this in this way:

     

    Code Snippet

    var previous = from pi in db.Photos

    where pi.PhotoID < photo.Single().PhotoID

    select new { pi.PhotoID };

    var next = from ni in db.Photos

    where ni.PhotoID > photo.Single().PhotoID

    select new { ni.PhotoID };

    if (previous.Count() == 0)

    {

    PreviousAreaEnd.Text = "' coords='0,4,24,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + next.Max(ni => ni.PhotoID).ToString() + "' />";

    NextAreaEnd.Text = "' coords='54,4,78,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + next.Min(ni => ni.PhotoID).ToString() + "' />";

    }

    else

    {

    PreviousAreaEnd.Text = "' coords='0,4,24,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + previous.Max(pi => pi.PhotoID).ToString() + "' />";

    if (next.Count() == 0)

    {

    NextAreaEnd.Text = "' coords='54,4,78,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + previous.Min(pi => pi.PhotoID).ToString() + "' />";

    }

    else

    {

    NextAreaEnd.Text = "' coords='54,4,78,29' href='?lang=" + CultureInfo.CurrentUICulture + "&amp;groupby=" + Request.QueryString["groupby"] + "&amp;groupid=" + Request.QueryString["groupid"] + "&amp;photo=" + next.Min(ni => ni.PhotoID).ToString() + "' />";

    }

    }

     

     

    Thursday, August 7, 2008 6:14 PM
  • It sounds like you might want to implement a paging implementation. In this case, your query could return 3 items. Make sure to set the order explicitly though to ensure that the items will stay in the same order.

     

    var photos = db.Photos.Skip(CurrentPhotoIndex-2).Take(3).ToList();

    var previous = photos[0];

    var current = photos[1];

    if (photos.Count>2) {var next = photos[2];}

     

    You would just be responsible for retaining the CurrentPhotoIndex in this scheme.

     

    Jim Wooley

    www.ThinqLinq.com

    Thursday, August 7, 2008 7:18 PM
    Moderator
  • Wednesday, December 3, 2008 10:30 AM