How do I solve these problems (linq to SQL)

Question

0.00/5 (No votes)

See more:

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?

C#

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

Posted 28-Aug-16 10:13am

s23user

Updated 28-Aug-16 11:01am

David_Wimbley

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-08-28T11:01:00

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

C#

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:

C#

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:

C#

//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[^]:

C#

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

[/EDIT#2]