Query issue

Tauseef A

hi guys i am using entity frame work and working in c# actually i m new to linq and i have the following three tables 1-Store 2-Prices 3-Items Stores -----> Prices <----------- Items in stores table there is a Primary key named StoreID which is a foreign key in the Prices table and in the prices table there is a ItemID field which is Primary key in the Items table. actually i want to grab all the prices,stores and items data but the problem is that there are multiple rows in the prices table and i want to get the latest row order by date, means i want to get only single and latest row from prices table relating to each storeid in the store table and from that prices data i want to get information from the items table how can i do that? regards,

Tauseef A Khan MCP Dotnet framework 2.0.

Dan Mos

your lack of details on the table fields make it hard to give a proper answear. In the "solution" I'm going to show I assumed that you declared a class that mapped each table in the DB. NOT LINQ to SQL but LINQ to Objects. First gets the data using something like:

private List GetStores()
.
.
.
cmd.CommandText = "EXEC GetStores";//GetStores is the StoredProc

dr = dr.ExcecuteReader();
while(dr.Read()){
curr = new Store();
curr.StoreID = dr.GetString(0);
.
.
.
stores.Add(curr);
}
.
.
.
return stores;

Hope that this will help:

var result = from itm in items
from store in stores
from price in prices
where price.ItemId.Equals(itm.ItemID)
where price.StoreID.Equals(store.StoreID)
where price.Date.Equals(GetLatestDate(prices, itm.ItemID))
select new
{
StoreID = store.StoreID,
Price = price.Price,
ItemId = itm.ItemID,
ItemDescription = itm.Description
};

And here is the helper function GetLatestDate. Something like:

private DateTime GetLatestDate(List<Prices> prices, Item itm)
    {
        DateTime latest = new DateTime(1900, 1, 1);
        foreach (var price in prices)
        {
            if (price.Date.CompareTo(latest) > 0)
            {
                latest = price.Date;
            }
        }
        return latest;
    }

Hope it helps. You can modify to use Entity framework or directly an SQL Stored Proc.

Dan Mos

I was in a hurry and forgt a small detaile in the GetLatestDate method. this is the good GetLatestDate:

private DateTime GetLatestDate(List<Prices> prices, Item itm)
{
DateTime latest = new DateTime(1900, 1, 1);
foreach (var price in prices)
{
if ((price.Date.CompareTo(latest)>0) && (price.ItemID.Equals(itm)))
{
latest = price.Date;
}
}
return latest;
}