linq query issues multiple tables

Question

Here is what I have guys/girls...I have 3 tables as follows (only included important columns):

ITEMS:

ItemId  Name            ItemLocationId
20      Portal Orange   12  
21      Portal Blue     13

ITEMLOCATIONS:

ItemLocationid  ItemId  CreateDate              LocationIf
13              21      3/26/2017 2:19:15 AM    5
14              20      3/27/2017 6:25:45 PM    6
15              21      3/31/2017 12:17:25 AM   6
16              21      3/31/2017 12:18:42 AM   5
17              21      3/31/2017 12:20:23 AM   6

LOCATIONS

LocationId  Name
5           Storage Room
6           Boss Room

My issue lies in the itemlocations table...I only need the most recent ItemLocation in the table...The others are for historical value...here is the query I am running now

Dim i = From r In mydb.Items
        Join il In mydb.ItemLocations On r.ItemLocationId Equals il.ItemLocationId
        Join l In mydb.Locations On il.LocationId Equals l.LocationId
        Where r.CompanyId = UserPro.CompanyId
        Select r.ItemId, r.Name, Location = l.Name

this is returning the first itemlocation in the table for that item...how do I get only the one with then most recent

use `OrderBy descending ItemlocationId` – Krsna Kishore Mar 31 '17 at 05:17 — Krsna Kishore, Mar 31 '17 at 05:17

jitender · Answer 1 · 2017-04-04T04:16:26.390

1

What about this one

Dim groupQuery=from il mydb.ItemLocations
  .group il by il.ItemId into gl
               select gl.orderByDescending(g=>g.CreateDate).First();


Dim i = From r In mydb.Items
        Join il In groupQuery On r.ItemLocationId Equals il.ItemLocationId
        Join l In mydb.Locations On il.LocationId Equals l.LocationId
        Where r.CompanyId = UserPro.CompanyId
        Select r.ItemId, r.Name, Location = l.Name

edited Apr 04 '17 at 04:16

answered Mar 31 '17 at 07:31

jitender

10,238
1
18
44

I can't get this to convert to VB – Guy Cothal Apr 01 '17 at 04:32
@GuyCothal i am not sure will that work or not please check the edit also this may help http://stackoverflow.com/a/7456167/5621827 – jitender Apr 01 '17 at 06:26
I tried that but it shows an error on g.CreatedDate – Guy Cothal Apr 04 '17 at 03:59
use gl.orderByDescending(g=>g.CreateDate).First(); – jitender Apr 04 '17 at 04:16

score 1 · Accepted Answer · answered Apr 06 '17 at 14:56

did not have database available so sketched answer for you in LinqPad. You need to select by date:

void Main()
{
List<Item> Items = new List<Item> { new Item { ItemID=20, ItemLocationID=12, Name="Portal Orqange"},
new Item{ ItemID=21, ItemLocationID=13, Name="Portal Blue"}};

List<ItemLocation> ItemLocations = new List<ItemLocation> { 
                                    new ItemLocation {ItemLocationID=13,  ItemId=21,  CreateDate=DateTime.Parse("3/26/2017 2:19:15 AM"),  LocationId=5},
                                    new ItemLocation {ItemLocationID=14,  ItemId=20,  CreateDate=DateTime.Parse("3/27/2017 6:25:45 PM"),  LocationId=6},
                                    new ItemLocation {ItemLocationID=15,  ItemId=21,  CreateDate=DateTime.Parse("3/31/2017 12:17:25 AM"),  LocationId=6},
                                    new ItemLocation {ItemLocationID=16,  ItemId=21,  CreateDate=DateTime.Parse("3/31/2017 12:18:42 AM"),  LocationId=5},
                                    new ItemLocation {ItemLocationID=17,  ItemId=21,  CreateDate=DateTime.Parse("3/31/2017 12:20:23 AM"),  LocationId=6},
                                    };
List<Location> Locations = new List<Location> { new Location { LocationID=5, Name="Storage Room"},
                                                new Location { LocationID=6, Name="Boss Room"}
                                                };

Items.Join(ItemLocations, i => i.ItemID, il => il.ItemId, (i, il) => new { i, il }).OrderByDescending(i =>i.il.CreateDate )
    .Join(Locations, iil => iil.il.LocationId, il=>il.LocationID, (lc, c) => new {lc,c}).FirstOrDefault()
    .Dump();


}

// Define other methods and classes here
public class Item
{
public int ItemID { get; set; }
public string Name { get; set; }
public int ItemLocationID { get; set;}

}

public class ItemLocation
{
public int ItemLocationID { get; set; }
public int ItemId { get; set; }
public DateTime CreateDate { get; set; }
public int LocationId { get; set;}
}

public class Location
{
public int LocationID { get; set; }
public string Name { get; set;}
}

When ready to code, just replace Dump() which is LinqPad specific to Select. Result as follows:

linq query issues multiple tables

2 Answers2