LINQ select from table where fieldValue is in another tables list of values

c# entity-framework-6 linq

Question

dbContext

class Table1

public string Id1 { get; set; }
public string Field { get; set; }
public string Id2 { get; set; }
public virtual Table2 Table2 { get; set; }

class Table2

public string Id2 { get; set; }
public string Field { get; set; }

I need to search all records from Table1 where Table1Field is in the other table list of values.

For example first I select all values which matches this search parameter

IQueryable<Table2> listOfvalues = from b in dbContext.Table2 
                                  where b.Field1.Contains(searchParam) 
                                  select b;
result = listOfvalues.ToList();

And now I need to write code which returns all fields from Table1 where Field is in that listOfValues.

1
1
1/6/2017 5:36:36 PM

Accepted Answer

I suppose you are looking for all items whose particular field matches another field in your already avialable list . you could do something similar.

dbContext.Table1.Where(p => listOfvalues.Any(l => p.Field == l.Field)).ToList();

EDIT Try using Contains

   var values = listOfvalues.Select (x => x.Field ).ToList();
   dbContext.Table1.Where(p => values.Contains(p.Field)).ToList();
5
10/7/2016 9:53:03 AM

Popular Answer

As you have a navigation property you just need to load the data.

for example:

using(dbContext = dbContext.CreateContext())
{ 
    var foo = ...;
    var results = dbContext.Table1Set.Where(t => t.table2.Field == foo);
}

As you are still within the context you can use lazy loading due to your virtual property. If you are no longer within the context scope when executing your check then you will have to use eager loading.

using(dbContext = dbContext.CreateContext())
{ 
    var setData = dbContext.Table1Set().Include(t => t.Table2);
}

var foo = ...;
var results = setData.Where(t => t.Table2.Field = foo);

This can be useful when you load your data set in a different method and return only the Enumerable collection.

for more information on loading related entities, this link will help:

https://msdn.microsoft.com/en-gb/data/jj574232.aspx



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow