CODEWITHSUNDEEP
c#linqentity-framework-6
Songaila
Songaila

Reputation: 219

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

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.

Upvotes: 2

Views: 27101

Answers (4)

Songaila
Songaila

Reputation: 219

Data structure:

Table1 

class Journal
int JournalId {get; set;}
Date JournalDate {get; set;}
string OwnerId {get; set;}

Table2

class Event
string EventId {get; set;}
string EventName {get; set;}

I found a simple solution but also meet another problem ...

First of all I get that List of values

var events = DBContext.Events.Where(o => o.EventName.Contains(searchParam)).AsQueryable();
var values = events.Select(x => x.EventID).ToList();

I only know how to put query results to list in foreach cycle. I created now List and put there all values where events in list of values I need

JournalList  = new List<Journal>();
 foreach (string eventId in values)
  {
    var results = DBContext.Journals.Where(j => j.eventID.Equals(eventId)).ToList();
 RegJournalList.AddRange(results);
  }

 GroupedList = JournalList.Select(t => new JournalGrouped()
                    {
                        JournalID = t.JournalID,
                        JournalDate = t.JournalDate
                   }).Distinct().ToList();

But for me is better me is better to have 

var journals = DBContext.Journals.AsQuarable();

not JournalList. Is there any possibilities to get matching data not in list but like a query, because I need to filter it after that??

Upvotes: 0

OnurBulbul
OnurBulbul

Reputation: 431

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

This will work for your case.

Upvotes: 1

Stefan William-Worrall
Stefan William-Worrall

Reputation: 713

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

Upvotes: 2

Ashley John
Ashley John

Reputation: 2453

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();

Upvotes: 6

Related Questions