如何对照Server数据库检查linq查询结果是否已经存在？

Question

我使用active目录获取所有部门，并使用linq查询过滤不同的部门，下面是我的代码

private static DomainController GetDomainController(string domainpath)
{
    var domainContext = new DirectoryContext(DirectoryContextType.Domain, domainpath);
    var domain = Domain.GetDomain(domainContext);
    var controller = domain.FindDomainController();
    return controller;
}

private static MyMethod()
{
    var domainController = GetDomainController(ActiveDirectorySettings.DomainPath);

    // Lookup the information in AD
    var ldapEntry = new DirectoryEntry(string.Format("LDAP://{0}", domainController)) { AuthenticationType = AuthenticationTypes.Secure | AuthenticationTypes.FastBind };
    DirectorySearcher ds;

    ds = new DirectorySearcher(ldapEntry)
    {
        SearchScope = SearchScope.Subtree,
        Filter = "(&" + "(objectClass=user)" + "(department=" + departmentname + "*))"
    };

    ds.PropertiesToLoad.Add("department");
    if (ds.FindAll().Count >= 1)
    {
        //DataSet du = DataReader.CheckAdUserExist();
        var results = ds.FindAll();
        var uniqueSearchResults = results.Cast<SearchResult>().Select(x => GetProperty(x,"department")).Distinct();
        addUsersList.AddRange(uniqueSearchResults.Select(departmentName => new UsersAndDepartments
        {
            UserDepartment = departmentName
        }));
    }
}

我想用数据库检查linq查询结果是否已经存在，我不知道怎么做？

Answer 1

如果您想要使用SqlConnection创建一个简单的数据库连接，您只需要使用从AD请求收到的department参数查询您的DB。

try{
    SqlConnection connection = new SqlConnection("YourConnectionstring");
    connection.Open();
    //Your connection string can be found through your Server DB
    //Now you go through your SearchResultCollection populated by SearchResult objects
    foreach(SearchResult res in uniqueSearchResult){
       SqlCommand cmd = new SqlCommand("Select * from yourTable where department=" +res.Properties["department"][0].ToString() + "", connection);
       SqlDataReader reader = cmd.ExecuteReader(); 
       //Here you verify if there are corresponding rows in your table 
       //with the request you have executed
       if(!reader.HasRows()){
           //If you have not found corresponding rows, then you add the department to your
           //list
           addUsersList.Add(res.Properties["department"][0].ToString());
       }
    }  
    connection.close();
}catch(Exception e){
    Console.WriteLine("Exception caught : \n\n" + e.ToString();
}

这应该能行。这方面有很多教程，但是如果您提出了大量的请求--我不建议使用此连接方法--您只会浪费太多的时间/组织，也许可以尝试使用像实体框架这样的持久性框架：

https://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C

希望这能回答你的问题！

Answer 2

，这是我的解决方案，我自己解决了，

private static DomainController GetDomainController(string domainpath)
{
    var domainContext = new DirectoryContext(DirectoryContextType.Domain, domainpath);
    var domain = Domain.GetDomain(domainContext);
    var controller = domain.FindDomainController();
    return controller;
}

private static MyMethod()
{
    var domainController = GetDomainController(ActiveDirectorySettings.DomainPath);

    // Lookup the information in AD
    var ldapEntry = new DirectoryEntry(string.Format("LDAP://{0}", domainController)) { AuthenticationType = AuthenticationTypes.Secure | AuthenticationTypes.FastBind };
    DirectorySearcher ds;

    ds = new DirectorySearcher(ldapEntry)
    {
        SearchScope = SearchScope.Subtree,
        Filter = "(&" + "(objectClass=user)" + "(department=" + departmentname + "*))"
    };

    ds.PropertiesToLoad.Add("department");
    if (ds.FindAll().Count >= 1)
    {
        // getting list of all departments from the database
        var departmentsList = AllDepartments();

        // getting list of all departments from active directory
        var results = ds.FindAll();

        // filtering distinct departments from the result
        var uniqueSearchResults = results.Cast<SearchResult>().Select(x => GetProperty(x,"department")).Distinct();

        // here firstly i am getting the department list from the database and checking it for null, then using linq query i am comparing the result with ad department results
        if (departmentsList != null)
        {
            addUsersList.AddRange(from sResultSet in uniqueSearchResults
            where !departmentsList.Exists(u => u.UserDepartment == sResultSet)
            select new UsersAndDepartments
            {
                UserDepartment = sResultSet
            });
        }
        else
        {
            addUsersList.AddRange(uniqueSearchResults.Select(departmentName => new UsersAndDepartments
            {
                UserDepartment = departmentName
            }));
        }
    }
}