how to make condition dynamically?

Question

I am using entityframework core,I want to query something dynamically.

public class Standard
{
public int ProductId{get;set;}
public int StandardId{get;set;}
}

there is a List<int[]>() which is all the StandardIds

I want to query all the data that emmm what should I say?

List<int[]>standards();
var query=from i in _context.Standard
group i by i.ProductId into g
where g.Count(x=>standards[0].Contains(x.StandardId)&&x.ProductId==g.Key)==standards[0].Length)
select g.Key;

I only can use standards[0] or standards[1] or .....

but I can not use standards.Any();

so I want to use

g.Count(x=>standards[0].Contains(x.standardId))==standards[0].Length)&&g.Count(x=>standards[1].Contains(x=>x.standardId))==standards[1].Length);

can I use a for?

for(var i=0;i<standards.Count;i++)
{
//here to make the sentence?
}

Answer 1

SQL Server doesn't have arrays, only tables. to perform your query with set operations, you will need to pass the list as table value variable and use FromSql.

lists of lists are implements as relationship tables, but you can probably get by with just adding a set number column to the list.

CREATE TYPE StandardIdType AS TABLE
( 
    SetId int,
    StandardId int
);

and a sp something like:

create procedure QueryStandard
  @list StandardIdType readonly
AS
set nocount on
with s AS
(
    select *
    from 
    (
        select ProductId, count(*) as Length from Standard s
        group by productid
    ) as p,
    (
        select SetId from @list 
        group by SetId
    ) as l
), match AS
(
    select s.ProductId, s.Length, s.SetId ,count(*) as MatchCount
    from s
    join @list l
        on l.SetId = s.SetId and l.StandardId in 
        (
            select StandardId from Standard s1
            where s1.ProductId = s.ProductId
        )
    group by s.ProductId,s.Length,s.SetId
), result as
(
    select ProductId from match
    where length = MatchCount
    group by ProductId
)
select * 
from result;