在LINQ TO SQL中选择列的值

本文关键字：选择 LINQ TO SQL | 更新日期: 2023-09-27 18:26:28

我有一个链接到SQL的查询，就像这个

var password = (from userAccounts in myDb.Physicians
                            where userAccounts.Phy_UserName == txtUserName.Text
                            select userAccounts).FirstOrDefault();

我想用该查询选择一个特定的列，我想检索userAccounts.Password、userAccounts.Phy_FName、userAccounts.Phy_Password等的行值。

是否允许password.Phy_FName？假设我们有结果？如何选择一个值？

在LINQ TO SQL中选择列的值

你说得对。您的Phy_UserName需要是数据库中的一列。它返回一个Physicians对象，您可以获得如下每个值。

// `var physician` is the same as `Physician physician` 
// where you're declaring a Physician object from your DBML file
// we're using `p` for `physician` in the lambda select function
var physician = (from p in myDb.Physicians
                 where p.Phy_UserName == txtUserName.Text
                 select p).FirstOrDefault();
// now that you've got a `physician` object, you can use the properties
// within the object as follows..
var password = physician.Password;
var firstName = physician.Phy_FName;
// etc
// please note that the physician object you have currently has ALL of the 
// database fields for that particular physician

现在，如果你选择只选择你需要的属性，你会有点不同。

// first you declare a poco to store only the needed properties.
public class PhysicianViewModel {
    public string Password {get; set;}
    public string FirstName {get; set;}
}
// then you run your select query and drill down to grab
// only the required data fields from the database
var physicianViewModel = (from p in myDb.Physicians
                          where userAccounts.Phy_UserName == txtUserName.Text
                          select new PhysicianViewModel { 
                              Password = p.Password,
                              FirstName = p.Phy_FName }).FirstOrDefault();
// and using them is the same as the above example
// the only difference here is that the `physicianViewModel` doesn't 
// contain ANY properties other than the ones you specified.
var password = physicianViewModel.Password;
var firstName = physicianViewModel.FirstName;

编辑

根据您下面的评论，这只是关于防止null值插入数据库中的string（nvarchar）字段的一点旁白。

最简单的方法是将所需的属性添加到属性中，并确保在提交之前对其进行验证

public class Physician {
    [Required]
    public string Password {get; set;}
    public string Phy_FName {get; set;}
    public string Phy_LName {get; set;}
}

如果在数据库中将allow null设置为false，然后更新DBML文件以反映更改，则[required]属性将自动添加到属性中。

var physician = (from p in myDb.Physicians
                 where p.Phy_UserName == txtUserName.Text
                 select p).FirstOrDefault();
var password = physician.Password;
var fName = physician.Phy_FName;

 var password = (from userAccounts in myDb.Physicians
                 where userAccounts.Phy_UserName == txtUserName.Text
                 select new { 
                          Password = physician.Password,
                          FullName = physician.Phy_FName  }).FirstOrDefault();
相关文章：