Cannot get Linq Query method to work with contains [closed]-CodePudding

I am attempting to use the results from one query to provide a list of acceptable values for another query. I am using query method because I am more comfortable with it. The vars morscontent provides a list with each iteration having multiple members, but I do not seem to be able to access them as a whole, without going to one of the individual counts. For example: morsContent[1].MORACODE Works, but I don't just one the instance in the first item but all of them to be searched against. This is my non working code:

using (var a2Entity = new A2_Data_UATEntities())
{

    var morsContent = (from a in a2Entity.MORDetails
                        where a.MORCODE == this.dropDown1.SelectedItem.Label
                        select a ).ToList();

    var dataResults = (from b in a2Entity.SCADA_DATA
                        where b.COLDATE >= dateCheck1 && b.COLDATE <= dateCheck2
                        && morsContent.Contains(b.acode)
                        && morsContent[1].MORACODE
                        select b).ToList();
}

CodePudding user response：

With linq you can do something like this.

    var dataResults = a2Entity.SCADA_DATA.Where(x => 
    x.COLDATE >= dateCheck1
     && x.COLDATE <= dateCheck2
     && morsContent.Contains(x.acode)
     && morsContent.Where(y => y.MORACODE == x.??).Any())
    .ToList()

CodePudding user response：

Maybe you could use a subquery for it and Entity Framework would resolve easily. I am not sure if it is the best case for it given you didn't provide more details about dataTypes and so on.

var dataResults = (from b in a2Entity.SCADA_DATA
                    let codes = (from a in a2Entity.MORDetails
                                 where a.MORCODE == this.dropDown1.SelectedItem.Label
                                 select a.MORACODE /* not sure what prop you need */)
                    where b.COLDATE >= dateCheck1 && b.COLDATE <= dateCheck2
                    && codes.Contains(b.acode)
                    select b)
                    .ToList();

Depending how many items you have on the first query, using a array.Contains(element) will generate an IN statement binding all the elements of array into your query. IT can be bad for you execution plan on database level, plus, some limitations depending on what database you use (max items you can use on IN). Using a subquery the database will manage it for you, but, yes, it is something to test against your model to be 100% sure what is the best choice.