locked
linq multiple field search RRS feed

  • Question

  • I have EF Employee table with fields EmpNo, FirstName, LastName, Email. And to create LINQ to search all columns and return existing record. If one or more fields is null or empty the return result based on existing data. I wrote code:

            public static int Search(EmployeeDt emp)
            {
                using (EF.Model1 context = new EF.Model1)
                {
                    List<string> employee = context.Employees.Where(a => (a.EmpNo == null || a.EmpNo == emp.EmpNo)
                     && (b => (b.FirstName == null || b.FirstName == emp.FirstName)
                     && (c => (c.LastName == null || c.LastName == emp.LastName)).ToList();
                    return result;
                }
            }

    But code which I tried to write isn't correct. How to write LINQ for my case? 

    Thanks.
    Wednesday, May 6, 2020 1:34 PM

All replies

  • Please provide some sample data, the results you're getting and what you expected.

    Given your query you would return any row where 
       EmpNo is null or it matches the search criteria
       AND FirstName is null or it matches the search criteria
       AND LastName is null or it matches the search criteria

    Personally I find this logic questionable. If you have an EmpNo then why would you need anything else? Also what if the search request doesn't have EmpNo, FirstName or LastName? Then your query would never return those rows. So the following request would always return no results (assuming the DB has no nulls) `new EmployeeDt (EmpNo = "10")`. That is probably not what you want.

    Additionally you should check your database. Does it really allow null for any of these columns? I could see maybe FirstName but EmpNo seems like a primary key, which cannot be null. LastName probably doesn't make sense as null either. You should only deal with nulls if the DB explicitly allows them. Personally I would make the code smarter and remove the null checks.

    var query = context.Employees.AsQueryable();
    if (!String.IsNullOrEmpty(emp.EmpNo))
       query = query.Where(x => x.EmpNo == emp.EpNo);
    if (!String.IsNullOrEmpty(emp.FirstName))
       query = query.Where(x => x.FirstName == emp.FirstName);
    if (!String.IsNullOrEmpty(emp.LastName))
       query = query.Where(x +> x.LastName == emp.LastName);
    
    var employees = query.ToList();

    The above code filters only on the criteria provided by the parameters. It doesn't worry about null since it wouldn't make sense anymore. A null last name, for example, would not match a specific criteria. If no criteria is provided it would return all rows.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 6, 2020 2:03 PM
  • Hi Michael. Thanks for reply.

    In my project I need:
       1. create a Data Layer method to search Employee by EmpNo or FirstName or LastName.
       2. create a Business Layer method to search Employees.

    I not good familiar with multi Layers project yet. What is difference between Data Layer and Business Layer? Can you tell me the method which you fixed and posted is it related to which layer? And if is Data Layer. How will look method to search Employees in Business Layer? I will very appreciate for explanation.

    Thanks.


    • Edited by zleug Wednesday, May 6, 2020 3:05 PM
    Wednesday, May 6, 2020 3:05 PM
  • Hello,

    Hopefully EmpNo is not a primary key as there should be an auto-incrementing column for this and if EmpNo is a property for users to see, still it should not be empty especially with Entity Framework Core using ValueGeneratedOnAdd via HasValueGenerator with an example for create an alpha numeric auto generated number so the field is never empty.

    As seems like first and last name should be required e.g.

    Then in the model use [Required] along with validation which could be in different places dependent on your code flow.

    (full model is here)

    public int EmployeeId { get; set; }
    [Required]
    public string LastName
    {
        get => _lastName;
        set
        {
            _lastName = value;
            OnPropertyChanged();
        }
    }
    [Required]
    public string FirstName
    {
        get => _firstName;
        set
        {
            _firstName = value;
            OnPropertyChanged();
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 6, 2020 3:40 PM
  • Hello Karen.

    In my project EmpNo similar to AccountNo in your case. Yes it is not primary key. Could you explain and suggest to do to create Search method in which layer in my case?

    Thanks.


    • Edited by zleug Wednesday, May 6, 2020 9:18 PM
    Wednesday, May 6, 2020 7:01 PM
  • Hi Michael. Thanks for reply.

    I used your code in method

    public static List<string> Search()
    {
       var query = context.Employees.AsQueryable();
       if (!String.IsNullOrEmpty(emp.EmpNo))
          query = query.Where(x => x.EmpNo == emp.EpNo);
       if (!String.IsNullOrEmpty(emp.FirstName))
          query = query.Where(x => x.FirstName == emp.FirstName);
       if (!String.IsNullOrEmpty(emp.LastName))
          query = query.Where(x +> x.LastName == emp.LastName);
    
       var employees = query.ToList();
    
       return employee;
    }

    In statement return red wave underline error for employee
    cannot implicitly convert type 'system.collections.generic.list to 'system.collections.generic.list

    How to fix the problem?

    Thanks.


    • Edited by zleug Thursday, May 7, 2020 3:22 AM
    Thursday, May 7, 2020 3:21 AM
  • I have no idea what your 'employee' variable is. That wasn't in the code I posted. The `employees` variable has the results of running the query you specified so I'm assuming `List<Employee>`. From there you have to decide what you want back. I assume you want this list so change the return type of the method to match the type of `employees`. Then change your return statement to return `employees` instead.

    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, May 7, 2020 4:11 AM