LINQ query with and and OR search criteria based on GUI

Anjali Agarwal 1,386 Reputation points
2023-05-30T01:22:45.0733333+00:00

I have the following search boxes on my web page:

User's image

This is my LINQ query:


 var transaction = (from sst in _Context.Transactions
                         join sstcnt in (
                             from ssti in _Context.TransactionItems
                             group ssti by ssti.TransactionId into g
                             select new { TransactionId = g.Key, NumItemId = g.Count() }
                         ) on sst.TransactionId equals sstcnt.TransactionId
                         join ssti in _Context.TransactionItems on sst.TransactionId equals ssti.TransactionId
                        where 
                               (ssti.TransactionId !=null && ssti.TransactionId.Equals(trans.TransactionId))||
                               (sst.EmailAddress !=null && sst.EmailAddress.Contains(trans.EmailAddress))||
                               (sst.Customer != null && sst.Customer.Contains(trans.Customer)) 

                               orderby sst.Created descending
                         select  new object[]
                         {
                                sst.TransactionId,
                                sst.Status,
                                sst.Created,
                                sst.StatusChangeDate,
                                sstcnt.NumItemId,
                                ssti.TransactionItemXml,
                                sst.Type
                         }
                         ).toList();

The search criteria can be NULL/empty or can be populated. I am filtering out the transactions by using seperate where conditions like this:

	where 
                               (ssti.TransactionId !=null && ssti.TransactionId.Equals(trans.TransactionId))||
                               (sst.EmailAddress !=null && sst.EmailAddress.Contains(trans.EmailAddress))||
                               (sst.Customer != null && sst.Customer.Contains(trans.Customer)) 

if I enter customer as "Jack" and email address as "test@test.com", I get the search records with name that matches "Jack" and the email part is ignored. How can I modify the query so that if the customer enters something in one field or several field, the search result should be based on if a match is found on any Single field or a combination of multiple fields. I tried putting and in the where clause too and that returned 0 results:

				where 
                               (ssti.TransactionId !=null && ssti.TransactionId.Equals(trans.TransactionId))&&
                               (sst.EmailAddress !=null && sst.EmailAddress.Contains(trans.EmailAddress))&&
                               (sst.Customer != null && sst.Customer.Contains(trans.Customer)) 

The above query returns 4 million records if there is no where clause so I need at least one where clause. Below are few values returned by the View. the user can fill out all the valyues and then the LINQ query should be filtered based on all the values passed on

User's image

with the above values, I want the LINQ where clause to be consdiering all the criteria:

phone Number, Custoimer Name, email address

any help will be greatly appreciated


        {
         
        
        
                    
                      
                           
                          
                       
                         
                       
|
                     
                           
                  
                         
                               
                              
                              
                          
                         
                               
                         
                              
                       
                
		 
  






User's image

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,375 questions
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,408 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,619 questions
{count} votes

Accepted answer
  1. Brijesh P Thankachan 85 Reputation points
    2023-05-30T09:08:49.02+00:00
    using System;
    using System.Linq;
    using System.Collections.Generic;
    public class Simple {
      public static void Main() {
    	  	  
    	List<Person> persons = new List<Person>
    	{
    		new Person { Name = "Burke", Email = "burke@example.com", Zip = "12345" },
    		new Person { Name = "Laptop", Email = "laptop@example.com", Zip = "54321" },
    		new Person { Name = "Computer", Email = "computer@example.com", Zip = "67890" },
    		new Person { Name = "Mobile", Email = "mobile@example.com", Zip = "09876" },
    		new Person { Name = "Ahemed", Email = "ahemed@example.com", Zip = "45678" },
    		new Person { Name = "Sania", Email = "sania@example.com", Zip = "23456" },
    		new Person { Name = "Kungada", Email = "kungada@example.com", Zip = "98765" },
    		new Person { Name = "David", Email = "david@example.com", Zip = "54321" },
    		new Person { Name = "United", Email = "united@example.com", Zip = "12345" },
    		new Person { Name = "Sinshia", Email = "sinshia@example.com", Zip = "67890" }
    	};
    	  
    	string name = "Sinshia";
    	string email = "sinshia@example.com";
    	string zip = "";
    	 
    	var query = from s in persons
                where (string.IsNullOrEmpty(name) || s.Name == name) &&
                      (string.IsNullOrEmpty(email) || s.Email == email) &&
                      (string.IsNullOrEmpty(zip) || s.Zip == zip)
                select s;
    
     
        foreach (var item in query)
          Console.WriteLine(item.Name);
      }
    }
    
    public class Person
    {
        public string Name { get; set; }
        public string Email { get; set; }
        public string Zip { get; set; }
    }
    

    try like this

    2 people found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful