我该如何解决这些问题（linq to SQL） [英] How do I solve these problems (linq to SQL)

问题描述

我是编程新手。我试图获得以下问题（以粗体字母表示）。我需要在我的代码中做些什么更改来实现这些？

我的代码：
  public   static   int  CountEmployeesByName（ string  [] namesToSearch）
 {
 使用（ApplicationEntities context =  new  ApplicationEntities（））
 {
  var  queries =  new  List< ; IEnumerable的<雇员>>（）; 
  string  nameParam; 
  foreach （字符串名称 in  namesToSearch）
 {
 nameParam = name; 
  //  按名称搜索员工 
 queries.Add（（来自 e  in  context.employees 
  where  e.name == nameParam 
  select  e））; 
} 
 返回 queries.Sum（q = >  q.Count（ ））; 
} 
} 
 
  public   static   void  UnitTests（）
 { //  填充测试数据 
 使用（ApplicationEntities context =  new  ApplicationEntities（））
 {
 context.Database.ExecuteSqlCommand（ @  TRUNCATE TABLE employees 
 INSERT INTO employees（name ）VALUES（'Niño'）; 
 INSERT INTO员工（姓名）VALUES（N'?????'）; 
 SET ANSI_WARNINGS OFF; 
 INSERT INTO员工（姓名）VALUES（ 'VeryLongName'）; 
 INSERT INTO员工（姓名）VALUES（'Name1'）; 
 INSERT INTO员工（姓名）VALUES（'Name2'）; 
 INSERT INTO员工（姓名）VALUES （ '尼克'）;）; 
} 
 
  //  问题 
  //  该函数始终无法在namesToSearch数组中找到除姓氏之外的任何名称。 
 Debug .Assert（CountEmployeesByName（ new   string  [] {  Name1，  Name2，  NoName3}）==  2 ）; 
   //  它失败了。它找到0为什么？它不应该返回2吗？我在代码中做了哪些更改？  
  //  问题 
   //  正在从数据库服务器检索每个匹配的员工记录（非常低效） 。 
  //   SQL引擎应该进行计数。  
 Debug.Assert（CountEmployeesByName（ new   string  [] {  Nick，  Name1}）==  3 ）; 
   //  失败..它只计算2，为什么？  
 
  / *   //问题
  // sdo有什么变化我在函数中创建它不应该为每个//搜索名称运行查询。每次调用函数时它应该运行1个SQL语句。 
 Debug.Assert（CountEmployeesByName（new string [] {Nick，Name1}）== 3）; 
 
  //问题
 //一名员工可以被计算两次。它应该返回唯一员工的数量。 
 Debug.Assert（CountEmployeesByName（new string [] {Name1，name1}）== 1）; 
  //找到2，我做了哪些更改？ 
} 
 static void Main（string [] args）
 {
单元测试（）; 
}  

解决方案

< b>问题＃1

函数总是找不到namesToSearch数组中的姓氏。

 Debug.Assert（CountEmployeesByName（ new   string  [] {   Name1，  Name2，  NoName3}）==  2 ）; 

失败。它找到0为什么？它不应该返回2吗？我在代码中做了哪些更改？

嗯......你甚至不需要创建本地列表< IEnumerable< employee>> 能够获得名称与数组中字符串名称匹配的员工数。可以通过这种方式简化函数体：

  return  context.employees.Where（x => ; namesToSearch.Any（y => y == x））。Count（）; 

我希望，这可以解决你的其他问题问题。

---

如果你愿意要提供自定义比较，您需要实施Equals方法 [ ^ 然后，无论大小写，你都可以比较eployee的名字。



此时，您可以使用 String.Equals（）方法 [ ^ ]。

前面例如：

  //  参数为： 
  //   namesToFind {nick，Name1，Name2，NoName3};  
  //   nick  
  return  context.employee.Where（x => namesToFind.Any（y => String.Equals（y，x，StringComparison.OrdinalIgnoreCase）））。Count（）; 

以上代码应返回 3 。

[/编辑]

---

要返回不同的值，请使用 Distinct（ ）方法 [ ^ ]：

 返回n  context.employee.Where（...）。Distinct（）。Count（）; 

[/ EDIT＃2] 的

I am new to programming. I am trying to achieve to get following Questions (which is in bold letters). What changes do i need to make in my codes to achieve those?

My code:
public static int CountEmployeesByName(string[] namesToSearch)
{
using (ApplicationEntities context = new ApplicationEntities())
{
var queries = new List<IEnumerable<employee>>();
string nameParam;
foreach (string name in namesToSearch)
{
nameParam = name;
//search for employees by name
queries.Add((from e in context.employees
where e.name == nameParam
select e));
}
return queries.Sum(q => q.Count());
}
}
 
public static void UnitTests()
{ //Populate with test data
using (ApplicationEntities context = new ApplicationEntities())
{
context.Database.ExecuteSqlCommand(@" TRUNCATE TABLE employees
INSERT INTO employees (name) VALUES ('Niño');
INSERT INTO employees (name) VALUES (N'?????');
SET ANSI_WARNINGS OFF;
INSERT INTO employees (name) VALUES ('VeryLongName');
INSERT INTO employees (name) VALUES ('Name1');
INSERT INTO employees (name) VALUES ('Name2');
INSERT INTO employees (name) VALUES ('Nick');");
}
 
//Question 
//The function is always failing to find any but the last name in the namesToSearch array.
Debug.Assert(CountEmployeesByName(new string[] { "Name1", "Name2", "NoName3" }) == 2);
//It fails . it finds 0 why? Shouldnt it return 2? What do i make change in my code?
//Question 
//Every matching employee record is being retrieved from the database server (very inefficient).
//The SQL engine should do the counting.
Debug.Assert(CountEmployeesByName(new string[] { "Nick", "Name1" }) == 3);
//Fails.. it counts only 2, Why?
 
/* //Question 
//What changes sdo i make in The function so that it should not be running a query for each //search name. It should run exactly 1 SQL statement each time the function is called.
Debug.Assert(CountEmployeesByName(new string[] { "Nick", "Name1" }) == 3); 
 
//Question 
//One employee can be counted twice. It should return number of unique employees.
Debug.Assert(CountEmployeesByName(new string[] { "Name1", "name1" }) == 1);
//it finds 2, what changes do i make ?
}
static void Main(string[] args)
{
UnitTests();
}

解决方案

Question#1
The function is always failing to find any but the last name in the namesToSearch array.

Debug.Assert(CountEmployeesByName(new string[] { "Name1", "Name2", "NoName3" }) == 2);

It fails . it finds 0 why? Shouldnt it return 2? What do i make change in my code?

Well... you don't even need to create local List<IEnumerable<employee>> to be able to get count of employees which name is matched to the name of string in array. The body of function can be simplified this way:

return context.employees.Where(x=>namesToSearch.Any(y=>y==x)).Count();

I hope, that resolves your other issues.

---

[EDIT]
If you would like to provide custom comparison, you need to implement the Equals method[^].Then you'll be able to compare eployee's name no matter of upper/lower case.

At this moment, you can implement Equals method, using String.Equals() method[^].
For example:

//parameters are:
//namesToFind {"nick", "Name1","Name2","NoName3"};
//nick
return context.employee.Where(x=>namesToFind.Any(y=>String.Equals(y, x,StringComparison.OrdinalIgnoreCase))).Count();

Above code should return 3.
[/EDIT]

---

[EDIT#2]
To return distinct values, use Distinct() method[^]:

return context.employee.Where(...).Distinct().Count();

[/EDIT#2]

这篇关于我该如何解决这些问题（linq to SQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！