c# - Linq - complex query - list in a list -

i have class:

public class recipeline {     public list<string> possiblenames { get; set; }      public string name { get; set; }      public int index { get; set; } } 

i have list of multiple recipeline objects. example, 1 of them looks this:

name: apple possiblenames: {red delicious, yellow delicious, ... } index = 3 

i have table in db called tblfruit , has 2 columns: name , id. id isn't same index in class.

what want this: whole list of recipeline objects, find records in tblfruit name in possiblenames, , give me index of class , id in table. have list in list (a list of recipeline objects have list of strings). how can linq in c#?

i'm pretty sure there isn't going linq statement can construct create sql query data how want. assuming tblfruit doesn't have too data, pull down whole table , process in memory like...

var result = tblfruitlist.select((f) => new {id = f.id, index = recipelinelist.where((r) => r.possiblenames.contains(f.name)).select((r) => r.index).firstordefault()}); 

keeping in mind index 0 if there isn't recipeline tblfruit's name in it's possiblenames list.

a more readable method doesn't one-line nasty linq statement is...

class resultitem {     int index {get;set;}     int id {get;set;} }  ienumerable<resultitem> getrecipefruitlist(ienumerable<fruititem> tblfruitlist, ienumerable<recipeline> recipelinelist) {     var result = new list<resultitem>();     foreach (fruititem fruititem in tblfruitlist) {         var match = recipelinelist.firstordefault((r) => r.possiblenames.contains(fruititem.name));         if (match != null) {             result.add(new resultitem() {index = match.index, id = fruititem.id});         }     }     return result; } 

if tblfruit has lot of data can try , pull down items have name in recipeline list's of possiblename lists like...

var allnames = recipelinelist.selectmany((r) => r.possiblenames).distinct(); var tblfruitlist = dbcontext.tblfruit.where((f) => allnames.contains(f.name));