Substituting foreach in linq

Question

I have a query that I want to omit foreach and make the whole in a query

I want to know how to implement both foreach and all the if clauses after the query inside the query?

Thanks for the help :)

foreach (var item in Filter.EPN)
                {
                    string H = item.Substring(0, 11);
                    string VH = item.Substring(14, 2);
                    string S = item.Substring(19, 11);
                    string VS = item.Substring(33);
                    ret1 = (from niv in Cniv
                            from dea in Dniv
                                .Where(d => niv.niv == d.niv)
                            let ccc = CTic.FirstOrDefault(c => c.Tic == niv.SMF || c.Tic == dea.SM)
                            join list in ListM on ccc.mar equals list.marE
                            where dea.time >= Filter.From && dea.time <= Filter.To &&
                                     niv.H == H && niv.VH == VH && niv.S == S && niv.VS == VS &&
                                     dea.ModelCode == Filter.Model
                            select new ReportData
                                 {
                                     niv = niv.niv,
                                     Ac = niv.Ac,
                                 });
                    if (Filter.mar != null && Filter.mar.Count > 0)
                    {
                        ret1 = ret1.Where(z => Filter.mar.Contains(z.mar));
                    }
                    if (ret1 != null && ret1.ToList().Count > 0)
                    {
                        ret.AddRange(ret1);
                    }
                }

Answer 1

You can create a SQL function that will accept the parameters that you want to add, so instead of a view create a function that will accept parameters and you can call that with linq.

Check out the article here: https://msdn.microsoft.com/en-us/library/bb386954(v=vs.110).aspx

So what'll you basically do is create the function that would accept a string for the market and return the values back. If you're looking for raw speed you could also do a sql command and get back a datatable. If you're just displaying data this would be the easiest method.

        string connection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(connection);
        con.Open();
        var cmd = new SqlCommand("SELECT * FROM myTable where market like" + market variable, con);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataTable dataTable = new DataTable();

Then you have a datatable with the results of your view which is fairly easy to manipulate.