Join DataTable with List<SomeObject>

Question

I have a DataTable and a List of objects. I need to return all rows in the DataTable where a property in the List is a certain value. The List is only used for filtering the datatable (but the filter column isn't containined in the datatable.

I'm sure this must be possible with link

The DataTable contains:

MembershipID  Username   Password   
1              blah        blah      
2              blah        blah      
3              blah        blah

My List contains:

   MembershipID  Profile1   Profile2   Profile3 DifferentID   
   1              blah        blah      blah    A             
   2              blah        blah      blah    B             
   3              blah        blah      blah    C

I need to return (as a DataTable) - eg: for GetUsersByDifferentID("B"):

    MembershipID  Username   Password   
    2              blah        blah      
   ...

I could get the second table as a DataTable if that would make it easier, but I think what I need is possible with LINQ. I just can't get my head around the magic syntax.

Answer 1

I am not sure about this because i couldnt understand that much about datatable which you mention about but can you try this

var usersByDifferentId = yourList.Where(a=> a["DifferentID"] == "B").Select(a=> new {a["UserName"],a["MemberShipID"],a["Password"]});

Answer 2

I think you want something like:

var memberIds = myList.Where(u => u.DifferentID == "B").Select(a => a.MembershipID);
var credentials = myDataTable.Where(d => memberIds.Contains(d.MembershipID));

There may be a more efficient way to do it, but something like that should work.

Answer 3

How about something like this. Let's say your List is a collection of this class:

public class SomeMemberClass
{
    public int MembershipId { get; set; }
    public char DifferentId { get; set; }
    //..some more properties
}

Then you can do something like this:

        DataTable table = new DataTable();
        table.Columns.Add("MembershipId", typeof(int));
        table.Columns.Add("UserName");
        table.Columns.Add("Password");

        List<SomeMemberClass> list = new List<SomeMemberClass>(); //or get the list from somewhere else...
        var differntIds = list.Select( s => s.DifferentId).Distinct();

        var result = table.AsEnumerable()
                              .Where( dt => differntIds
                              .Contains((int)dt["MembershipId"]))
                              .CopyToDataTable();

Basically, first get all the distinct DifferentId's and then use that to look through your table.

Answer 4

You can do it using a join:

List<ListItem> listItems = //whatever
DataTable dtItems = //whatever

IEnumerable<DataRow> matchingRows = listItems
    .Join( dtItems.AsEnumerable(),
      listItem => listItem.MembershipID,
      row => row.Field<int>("MembershipID"),
      (r,li) => new { DifferentId = li.DifferentId, Row = r })
    .Where( ji => ji.DifferentID == "B")
    .Select( ji => ji.Row);

Change the where clause to use the actual value you want to match...