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

Tummala Krishna Kishore
– Tummala Krishna Kishore

2017-03-31 05:17:33 +00:00
Commented Mar 31, 2017 at 5:17 — Tummala Krishna Kishore
– Tummala Krishna Kishore, Commented Mar 31, 2017 at 5:17

Yuri · Accepted Answer · 2017-04-06 14:56:27Z

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:

jitender · Accepted Answer · 2017-04-04 04:16:26Z

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 4, 2017 at 4:16

answered Mar 31, 2017 at 7:31

jitender

10.4k1 gold badge22 silver badges49 bronze badges

4 Comments

Guy Cothal Over a year ago

I can't get this to convert to VB

jitender Over a year ago

@GuyCothal i am not sure will that work or not please check the edit also this may help stackoverflow.com/a/7456167/5621827

Guy Cothal Over a year ago

I tried that but it shows an error on g.CreatedDate

jitender Over a year ago

use gl.orderByDescending(g=>g.CreateDate).First();

Collectives™ on Stack Overflow

linq query issues multiple tables

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related