C# - SQL - LINQ Query

Question

Having a few problems with this query ::

var q2 =
    from sd in db.ServerDrives
    where sd.DriveLetter == driveList[i].Name
    select sd;
ServerDrive existingServerDrives = q.First();
existingServerDrives.FreeSpace = driveList[i].FreeSpace;
//..
db.SubmitChanges();

My problem is that I don't know where to put the reference to [i] in.

Reference for [i] ::

for (int i = 0; i < driveList.Count; i++)

I can put it above the,

existingServerDrives.FreeSpace = driveList[i].FreeSpace;

And the line recognises it, but I need the whole query to recognise it due to the .(code below). line

where sd.DriveLetter == driveList[i].Name;

Does anyone know how I could possibly do this?

EDIT : FULL CODE::

class Program
{
    List<DriveInfo> driveList = DriveInfo.GetDrives().Where(x => x.IsReady).ToList<DriveInfo>(); //Get all the drive info
    Server server = new Server();  //Create  the server object
    ServerDrive serverDrives = new ServerDrive();

    public static void Main()
    {
        Program c = new Program();
        c.RealDriveInfo();
        c.WriteInToDB();
    }

    public void RealDriveInfo()
    {


        //Insert information of one server - You will need get information of all servers
        server.ServerID = 0; //Here is necessery put PK key. I recommend doing the SQL server will automatically generate the PK.
        server.ServerName = string.Concat(System.Environment.MachineName);

        //Inserts information in the newServers object
        for (int i = 0; i < driveList.Count; i++)
        {

            //Put here all the information to object Server                
            serverDrives.DriveLetter = driveList[i].Name;
            serverDrives.TotalSpace = driveList[i].TotalSize;
            serverDrives.DriveLabel = driveList[i].VolumeLabel;
            serverDrives.FreeSpace = driveList[i].TotalFreeSpace;
            serverDrives.DriveType = driveList[i].DriveFormat;
            server.ServerDrives.Add(serverDrives);

        }
    }

    public void WriteInToDB()
    {
        //Add the information to an SQL Database using Linq.
        DataClasses1DataContext db = new DataClasses1DataContext(@"sqlserver");
        db.Servers.InsertOnSubmit(server);

        var q2 =
            from s in db.Servers
            where s.ServerName == "LAPTOP-043"
            select s;
        Server existingServers = q2.First();
        for (int i = 0; i < driveList.Count; i++)
            existingServers.ServerName = string.Concat(System.Environment.MachineName);
        //..
        for (int i = 0; i < driveList.Count; i++)
        {
            var q =
                from sd in db.ServerDrives
                where sd.DriveLetter == driveList[i].Name
                select sd;
            ServerDrive existingServerDrives = q.First();
            existingServerDrives.FreeSpace = driveList[i].FreeSpace;
            //..
            db.SubmitChanges();

What I am trying to do is have the query find a row with 'DriveLetter' being the same as the drive C:\ on this computer. The second part is then supposed to change the FreeSpace value and replace it with the new FreeSpace value that is retrieved from the console application.

Any feedback would be appreciated, Thanks in advance :)

Answer 1

Update: please read clarification in the end of answer for details about closure handling.

First things first. This code:

    for (int i = 0; i < driveList.Count; i++)
    {
        var q =
            from sd in db.ServerDrives
            where sd.DriveLetter == driveList[i].Name
            select sd;
        //...
   }

won't work irregardless of curly braces. The reason for that is closure of i (which is masked out by funny LINQ syntax). The easiest way to fix it is to store i value (or referenced value) in separate variable.

    for (int i = 0; i < driveList.Count; i++)
    {
        var j = i;
        var q =
            from sd in db.ServerDrives
            where sd.DriveLetter == driveList[j].Name
            select sd;
        //...
    }

This will work, but it looks loosy. Better:

    for (int i = 0; i < driveList.Count; i++)
    {
        var drive = driveList[i];
        var q =
            from sd in db.ServerDrives
            where sd.DriveLetter == drive.Name
            select sd;
        ServerDrive existingServerDrives = q.First();
        existingServerDrives.FreeSpace = drive.FreeSpace;
        //..
        db.SubmitChanges();
    }

But why would you need loop in first place? Why not foreach?

    foreach (var drive in driveList)
    {
        var q =
            from sd in db.ServerDrives
            where sd.DriveLetter == drive.Name
            select sd;
        ServerDrive existingServerDrives = q.First();
        existingServerDrives.FreeSpace = drive.FreeSpace;
        //..
        db.SubmitChanges();
    }

But this won't work too, be careful. Same trouble with closures. Solution is easy:

    foreach (var d in driveList)
    {
        var drive = d;
        var q =
            from sd in db.ServerDrives
            where sd.DriveLetter == drive.Name
            select sd;
        ServerDrive existingServerDrives = q.First();
        existingServerDrives.FreeSpace = drive.FreeSpace;
        //..
        db.SubmitChanges();
    }

But of course such things are better with projections:

    foreach (var d in driveList.Select(d => new {freeSpace = d.FreeSpace, existingServerDrives = db.ServerDrives.First(sd => sd.DriveLetter == d.Name)}))
    {
        d.existingServerDrives.FreeSpace = d.freeSpace;
        //..
        db.SubmitChanges();
    }

Clarification: In fact, I am not completely right about "will not work" part. It will if driveList is IQueryable, but it won't if it is IEnumerable (because the former uses expressions, and the latter - delegates). But because syntax is same, it is all too easy to make this error, so you still should not use loop variables inside lambda expressions irregardless of their real type.

Answer 2

not sure what you're trying to say or do, but how about this:

for (int i =0; i < driveList.Count; i++)
{
var q2 =
    from sd in db.ServerDrives
    where sd.DriveLetter == driveList[i].Name
    select sd;
ServerDrive existingServerDrives = q2.First();
existingServerDrives.FreeSpace = driveList[i].FreeSpace;
//..
db.SubmitChanges();
}

Answer 3

Jane Doe has the right idea. Just to add an explanation, it seems from your vague description you wrote this:

for (int i = 0; i < driveList.Count; i++)
var q2 =
    from sd in db.ServerDrives
    where sd.DriveLetter == driveList[i].Name
    select sd;
ServerDrive existingServerDrives = q.First();
existingServerDrives.FreeSpace = driveList[i].FreeSpace;
//..
db.SubmitChanges();

This is interpreted as follows:

for (int i = 0; i < driveList.Count; i++)
{
    var q2 =
        from sd in db.ServerDrives
        where sd.DriveLetter == driveList[i].Name
        select sd;
}

ServerDrive existingServerDrives = q.First();
existingServerDrives.FreeSpace = driveList[i].FreeSpace;
//..
db.SubmitChanges();

The variable i is only valid inside the loop body. The solution is to put the curly braces around all the code you wish to run in the loop and also you need to change q to q2.

for (int i = 0; i < driveList.Count; i++)
{
    var q2 =
        from sd in db.ServerDrives
        where sd.DriveLetter == driveList[i].Name
        select sd;

    ServerDrive existingServerDrives = q2.First();
    existingServerDrives.FreeSpace = driveList[i].FreeSpace;
    //..
    db.SubmitChanges();

}

Note also that this is not the most efficient way to update multiple rows because you are performing two database requests per update.