CODEWITHSUNDEEP
linq
Nick LaMarca
Nick LaMarca

Reputation: 8188

Combining Sub Queries Into 1 Query Linq

Is there a way I can rewrite the following query to make it just one query?

 try
            {
                var fileIds = (from f in context.SignalTo
                               where f.SignalFileID == 2
                               select new { f.GFileID }).ToList();



                foreach (var id in fileIds)
                {
                  var pp = (from p in context.ProjectFiles
                                   where p.FileID == id.GFileID &&  p.ProjectID == ProjectID
                                   select p);

                    if (pp != null)
                    {
                        ProjectFiles projectFile =(ProjectFiles) pp;
                        projectFile.MStatus = Status;
                        projectFile.DateLastUpdated = DateTime.Now;
                        context.SaveChanges();
                    }


                }
            }

Upvotes: 0

Views: 105

Answers (1)

Phil
Phil

Reputation: 42991

You can combine the two query parts of your code into one.

You would then need to loop over the result set, making your updates. You would then call context.SaveChanges to submit all changes in one batch.

I can't tell if your existing code actually runs or compiles, but you need something like this:

Get the list of file ids you're interested in:

var fileIds = from f in context.SignalTo
              where f.SignalFileID == 2
              select f.GFileID;

fileIds is at this point an IQueryable where I assume T is an Int. No query has been excuted yet.

Now you can do

var pps = from p in context.ProjectFiles
         where fileIds.Contains(p.FileID) &&  p.ProjectID == ProjectID
         select p;

Still no query executed.

Then iterate over the result set

foreach( var pp in pps ) // query executed now
{
    pp.MStatus = Status;
    pp.DateLastUpdated = DateTime.Now;
}

context.SaveChanges(); // batch of updates executed now

Upvotes: 1

Related Questions