Substitute Foreach in Linq Query

Question

0.00/5 (No votes)

See more:

I have a linq query in a foreach loop with some if clauses
I wanted you to help me how can I write them all in a query (not using foreach)
Number is a big table that I need to divide each line of it to four A,B,C,D and search them in another table and find the similars

C#

ret = new List<ReportData>();
    foreach (var item in Number)
    {
        string A = item.Substring(0, 11);
        string B = item.Substring(14, 2);
        string C = item.Substring(19, 11);
        string D = item.Substring(33);
        ret1 = (from a in Report
                where a.A == A && a.B == B
                && a.C == C && a.D == D &&   
                Filter.Type.Contains(a.Y)
                select new ReportData
                {
                    X = a.X,
                    Y = a.Y,
                });
      
       if (Filter.Ma != null && Filter.Ma.Count > 0)
          {
             ret1 = ret1.Where(z => Filter.Ma.Contains(z.Ma));
          }
       if (ret1 != null && ret1.ToList().Count > 0)
          {
             ret.AddRange(ret1);
          }
}

Posted 16-Jul-15 4:56am

Lalyka

Updated 16-Jul-15 5:01am

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-07-16T05:18:00

Solution 1

I hate linq syntax. I use extension syntax so I can build up queries to refine them.

Either way - This is my stab:

C#

var ret = Number.Select(item => new
{
    A = item.Substring(0, 11),
    B = item.Substring(14, 2),
    C = item.Substring(19, 11),
    D = item.Substring(33)
})
.SelectMany(n =>
    Report.Where(
        a => a.A == n.A && a.B == n.B
        && a.C == n.C && a.D == n.D &&
        Filter.Type.Contains(a.Y))
    .Select(a => new ReportData
        {
            X = a.X,
            Y = a.Y,
        }))
.Where(nr =>
    Filter.Ma == null || !Filter.Ma.Any() || Filter.Ma.Contains(nr.Ma)
).ToList();

I couldn't really test it as I don't know what Report, Filter or ReportData, really look like

Update: Broken down into stages:

C#

//Must be a var as the type is anonymous
var q1 = Number.Select(item => new
{
    A = item.Substring(0, 11),
    B = item.Substring(14, 2),
    C = item.Substring(19, 11),
    D = item.Substring(33)
}).AsQueryable(); //Not a requirement but should help optimize


//Select many is pretty darn optimal.  I don't see you getting a better return that this
var q2 = q1.SelectMany(n =>
    Report.Where(
        a => a.A == n.A && a.B == n.B
        && a.C == n.C && a.D == n.D &&
        Filter.Type.Contains(a.Y))
    .Select(a => new ReportData
        {
            X = a.X,
            Y = a.Y,
        })).AsQueryable();

//If the return type is the same a previous variable then you can reuse it.
var ret = q2.Where(nr =>
    Filter.Ma == null || !Filter.Ma.Any() || Filter.Ma.Contains(nr.Ma)
).ToList();

Posted 16-Jul-15 5:18am

Andy Lanng

Updated 16-Jul-15 6:24am

v2

Comments

Lalyka 16-Jul-15 12:08pm

Thanks a lot, it is working but may I know if it is possible to implement this
.Where(nr =>
Filter.Ma == null || !Filter.Ma.Any() || Filter.Ma.Contains(nr.Ma)
before.select?
and if I want to implement more than one if condition what should I do?

Andy Lanng 16-Jul-15 12:12pm

I don't see how. The object 'nr' is a direct result of the select. If "Ma" is something you can calculate or get without instantiating ReportData, then yes, you can.

The order shouldn't matter. It will get normalized automatically. The whole thing should only execute on "ToList()"

(ps. really? it worked without tweaks? I knew I was good but that surprised me ^_^ )

Lalyka 16-Jul-15 12:14pm

I am worried for the order because I need to optimize it, my first solution was so slow.

Andy Lanng 16-Jul-15 12:19pm

yes but linq is self, optimizing. The extension methods to not execute one by one, but make up a single query that it only executed when ToArray, or ToList or some other specific extension requires it.

It's easy to break it down though. I'll update the solution in a way that is easier to tinker with.

If you manage to get data for the where cause before the select then I would like to know if you do actually see any improvement ^_^

Lalyka 17-Jul-15 3:41am

Hi Andy

actually yesterday I was testing to see if teh query returns the right results but it does not, I want it to seach the database for every Filter.Type that in in a.Y
is it Filter.Type.Contains(a.Y)) the right command thoght?

Andy Lanng 21-Jul-15 4:21am

Hi, Long weekend. Sorry for late reply.

My code should do what your code does. I cannot test it as I do not know what the Filter class (or any other bespoke class you have) is. To get a full solution you will have to show me what these classes look like.

Try creating a test project with a version of these classes cut down to just what is used in the query. Add some data to them so I can see what they contain, then post that code (in full) in the question (use Improve Question below the question). The code should be compile-able.

At the end of the original question, add the text "UPDATE" then post the new code below.

With that I can give you a fully optimized solution ^_^

Lalyka 21-Jul-15 7:15am

Hi ANdy

it sees like teh problem is the view that is so slow, may I ask howcan I implement theconditions in view in away that before loading the view completely and after executing the query , we can pass parameters to the view?
this is my view query that I want to send parameter as "where"

SELECT dbo.X.niv, dbo.X.Operation,
FROM dbo.X INNER JOIN
dbo.deal ON dbo.X.niv = dbo.deal.niv INNER JOIN
dbo.tick ON dbo.X.smse = dbo.tick.Ticket OR
dbo.deal.Session = dbo.tick.Ticket INNER JOIN
dbo.Mark ON dbo.tick.Mrkt = dbo.Mark.Maks

Andy Lanng 21-Jul-15 10:33am

hmm - not really in the query. Your joins are all optimal (even the 'or'). You might try to make sure that the join columns are correctly indexed. I have no idea what your column types are, but if they are not 'int' then you are doing it wrong. Joins on columns with large or variable data types will be very slow and not at all scale-able.

PS: this looks neater:
SELECT dbo.X.niv, dbo.X.Operation,
FROM dbo.X x
INNER JOIN dbo.deal d ON x.niv = d.niv
INNER JOIN dbo.tick t ON x.smse = t.Ticket OR d.Session = t.Ticket
INNER JOIN dbo.Mark m ON t.Mrkt = m.Maks

Lalyka 21-Jul-15 11:05am

@andy Hi andy
I wanted to know if I have many joins in my query how can I write it the way you suggested me because the solutionfor making the query fast is to omit the view and write the query by accessing DBs directly

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

Andy Lanng 21-Jul-15 11:40am

(hit reply at the top of my comment to continue the thread)

There is no reason a view would be any slower than a direct query. What does matter is join data types, query (and view query) efficiency, Table indexing and how you query the data from the code behind.

I don't know what your join types are
I don't know how you are retrieving the data
I don't know what indexes are on your tables
I do know that your query efficiency is good
I don't know how you handle the data in the code behind because I do not know what your classes do.

Lalyka 21-Jul-15 11:54am

I have made an alert after and before this query and only this query is taking long. so I am thinking to either pass parameters to view or to write the query directly . to write the query directly I need to omit foreach to make it faster. and theabove query is the same as the old one with the difference that it is joing different databases together to get the data from

Andy Lanng 21-Jul-15 11:58am

I've explained my view. You are now ignoring me or we are talking at cross purposes. Either way I think we should draw a line under this thread here.

I wish you the best of luck ^_^

Substitute Foreach in Linq Query

1 solution

Solution 1

Add your solution here

Preview 0

Substitute Foreach in Linq Query

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us