C＃ - SQL - LINQ查询

Question

有这个查询::

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

我的问题是，我不知道放在哪里参考[i]于

参考了一些问题。为[I] ::

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

我可以把它上面的，

existingServerDrives.FreeSpace = driveList[i].FreeSpace; 

并且该行可以识别它，但由于。（代码如下），我需要整个查询来识别它。 line

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

有谁知道我怎么可能做到这一点？

编辑： 完整的代码::

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

我所要做的是让查询找到“驱动器号”是一样的驱动器C的一行：\此计算机上。然后，第二部分应该更改FreeSpace值并将其替换为从控制台应用程序检索到的新FreeSpace值。

任何反馈，将不胜感激，在此先感谢:)

Answer 1

不知道你想要说什么或做什么，但这个怎么样：

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 2

Jane Doe的看法是正确的。我想补充的解释，似乎从模糊的描述你写这样的：

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

这被解释如下：

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

变量i只是循环体中有效。解决方案是将大括号放在所有您希望在循环中运行的代码中，并且您还需要将q更改为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(); 

} 

还要注意，这不是更新多行的最有效方式，因为每次更新都执行两次数据库请求。

Answer 3

更新：有关关闭处理的详细信息，请在回答结尾处阅读说明。

第一件事第一件事。此代码：

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

无法使用大括号。原因是关闭了i（它被有趣的LINQ语法掩盖了）。修复它的最简单方法是将i值（或参考值）存储在单独的变量中。

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; 
     //... 
    } 

这将工作，但它看起来很宽松。更好：

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

但是，为什么你需要首先循环？为什么不是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(); 
    } 

但是这不行，太小心了。关闭同样的麻烦。解决方法很简单：

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

不过，当然这样的事情是有凸起更好：

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

澄清：其实，我并不完全正确的关于“将不工作”的一部分。它将如果driveList是IQueryable，但它将不会如果它是IEnumerable（因为前者使用表达式，后者 - 代表）。但是因为语法是一样的，所以这个错误非常容易，所以你不应该在lambda表达式中使用循环变量，不管它们的实际类型如何。