Manipulate Linq output

Question

I've got two queries which I union together in Linq like so;

var reWorkData = from a in db.IV30300s
                          join b in db.IV00101s on a.ITEMNMBR equals b.ITEMNMBR into t1
                          from b1 in t1.DefaultIfEmpty()
                          join c in db.IV30200s on a.DOCTYPE equals c.IVDOCTYP into t2
                          from c1 in t2.Where(c => a.DOCNUMBR == c.DOCNUMBR).DefaultIfEmpty()
                          where a.TRXLOCTN.ToUpper() == "QA" &&
                          a.DOCTYPE == 1 &&
                          (a.DOCDATE >= start && a.DOCDATE <= end) &&
                          a.TRXQTY > 0
                          select new { A = a, B = b1 };

            var newBuildData = from a in db.IV30300s
                               join b in db.IV00101s on a.ITEMNMBR equals b.ITEMNMBR into t1
                               from b1 in t1.DefaultIfEmpty()
                               join c in db.IV30200s on a.DOCTYPE equals c.IVDOCTYP into t2
                               from c1 in t2.Where(c => a.DOCNUMBR == c.DOCNUMBR).DefaultIfEmpty()
                               where a.TRXLOCTN.ToUpper() == "PRODUCTION" &&
                               a.TRNSTLOC.ToUpper() == "QA" &&
                               a.DOCTYPE == 3 &&
                               (a.DOCDATE >= start && a.DOCDATE <= end) &&
                               a.TRXQTY > 0
                               select new { A = a, B = b1 };

            var rawData = reWorkData.Union(newBuildData);

What I then need to do is perform a piece of logic checking on a.ITEMNMBR and change it's value depending on the outcome. This will then be used to group items together.

IS it simply a case of iterating through something, performing the logic and then making the change?

Answer 1

IS it simply a case of iterating through something, performing the logic and then making the change?

Yes, but if your data is placed in a SQL database it might be much more efficient to create SQL script to do what you want. Relational databases are really good at performing operations on big sets. Pulling the data to the client and updating records one at a time can be much slower.

Answer 2

IS it simply a case of iterating through something, performing the logic and then making the change?

Basically, yes, that will work. However, I'd recommend changing your last line to:

 var rawData = reWorkData.Union(newBuildData).ToList();

This will force the Union to evaluate fully, which allows you to make your changes, and do your grouping (which may require a second iteration, etc), without forcing multiple evaluations of your Union, or causing odd, potentially unexpected behavior.

In addition, if the data is already stored in a database, it would likely be more efficient to try to perform the logic on the server (as part of your query itself), and pull the data appropriately. This could, potentially, reduce the amount of data pulled across the wire (the "grouping" can happen first) and processing done on the client.