Dynamic Search Using Entity Framework

Question

I have a search screen with optional fields using Entity Framework, I want to build a dynamic query without selecting the object and filter on it.

I want to Optimize the following Existing Search, I don't want to select "var query = from p in context.APLCN_TRCKR select p;" at this stage because the application will be used by more than 100 people at once:

using (var context = new VASTEntities())
{
    var query = from p in context.APLCN_TRCKR select p;

    if (!string.IsNullOrEmpty(searchObj.CUST_NAME_X))
        query = query.Where(p => p.CUST_NAME_X == searchObj.CUST_NAME_X.Trim());

    if (!string.IsNullOrEmpty(searchObj.SURNM_X))
        query = query.Where(p => p.CUST_SURNM_X == searchObj.SURNM_X.Trim());

    if (!string.IsNullOrEmpty(searchObj.QUEUE_ID))
        query = query.Where(p => p.QUEUE_ID == searchObj.QUEUE_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.BP_ID))
        query = query.Where(p => p.BPID == searchObj.BP_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.UserID))
        query = query.Where(p => p.CURR_OWNR_USER_ID == searchObj.UserID.Trim());

    if (!string.IsNullOrEmpty(searchObj.APLCN_TRCKR_ID))
        query = query.Where(p => p.APLCN_TRCKR_ID == searchObj.APLCN_TRCKR_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.APLCN_STTS_ID))
        query = query.Where(p => p.APLCN_STTS_ID == searchObj.APLCN_STTS_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.CUST_ID))
        query = query.Where(p => p.CUST_ID == searchObj.CUST_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.CELL_ID))
        query = query.Where(p => p.CELL_ID == searchObj.CELL_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.ORIGN_ID))
        query = query.Where(p => p.ORIGN_ID == searchObj.ORIGN_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.ORGTN_CHANL_ID))
        query = query.Where(p => p.ORGTN_CHANL_ID == searchObj.ORGTN_CHANL_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.CR_DCSN_ID))
        query = query.Where(p => p.CR_DCSN_ID == searchObj.CR_DCSN_ID.Trim());

    if (!string.IsNullOrEmpty(searchObj.SBSA_CUST_I))
        query = query.Where(p => p.SBSA_CUST_I == searchObj.SBSA_CUST_I.Trim());

    if (!string.IsNullOrEmpty(searchObj.USER_ID_APP_CRTD))
        query = query.Where(p => p.USER_ID_APP_CRTD == searchObj.USER_ID_APP_CRTD.Trim());

    if (!string.IsNullOrEmpty(searchObj.RGION_ID))
    {
        int r = int.Parse(searchObj.RGION_ID.Trim());
        query = query.Where(p => p.RGION_ID == r);
    }

    if (!string.IsNullOrEmpty(searchObj.CR_REGION))
    {
        int x = int.Parse(searchObj.CR_REGION);

        if (x == 0)
        {
            // check 0 - not applicable or null
            query = query.Where(p => p.CR_REGION_ID == 0 || p.CR_REGION_ID == null);
        }
        else
        {
            query = query.Where(p => p.CR_REGION_ID == x);
        }
    }

    if (!string.IsNullOrEmpty(searchObj.Process_Type))
        query = query.Where(p => p.PRCES_TYPE_ID == searchObj.Process_Type.Trim());

    query.ToList();

    foreach (var a in query)
    {
         searchAppsObj.Add(Translator.TranslateReqObjToBO.TranslateDTOToSearchApp(a));
    }

    if (query.Count() == 0)
    {
        throw new Exception("No Applications Found.");
    }

    context.Connection.Close();
    return searchAppsObj;
}

I want to do something like this but this one is not working properly:

string cust_name_x = "", surname_x = "", queue_id = "", bp_id = "", user_id = "", aplcn_trckr_id = "",
               aplcn_stts_id = "", cust_id = "", process_type = "", cr_region = "", cell_id = "", Origin = "", region = "", channel = "", credit_verdict = "", sbsa_cust_id = "", app_creator_id = "";

            if (!string.IsNullOrEmpty(searchObj.CUST_NAME_X))
                cust_name_x = searchObj.CUST_NAME_X.Trim();
            if (!string.IsNullOrEmpty(searchObj.SURNM_X))
                surname_x = searchObj.SURNM_X.Trim();
            if (!string.IsNullOrEmpty(searchObj.QUEUE_ID))
                queue_id = searchObj.QUEUE_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.BP_ID))
                bp_id = searchObj.BP_ID;
            if (!string.IsNullOrEmpty(searchObj.UserID))
                user_id = searchObj.UserID.Trim();
            if (!string.IsNullOrEmpty(searchObj.APLCN_TRCKR_ID))
                aplcn_trckr_id = searchObj.APLCN_TRCKR_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.APLCN_STTS_ID))
                aplcn_stts_id = searchObj.APLCN_STTS_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.CUST_ID))
                cust_id = searchObj.CUST_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.Process_Type))
                process_type = searchObj.Process_Type.Trim();
            if (!string.IsNullOrEmpty(searchObj.CR_REGION))
                cr_region = searchObj.CR_REGION.Trim();
            if (!string.IsNullOrEmpty(searchObj.CELL_ID))
                cell_id = searchObj.CELL_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.ORIGN_ID))
                Origin = searchObj.ORIGN_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.RGION_ID))
                region = searchObj.RGION_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.ORGTN_CHANL_ID))
                channel = searchObj.ORGTN_CHANL_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.CR_DCSN_ID))
                credit_verdict = searchObj.CR_DCSN_ID.Trim();
            if (!string.IsNullOrEmpty(searchObj.SBSA_CUST_I))
                sbsa_cust_id = searchObj.SBSA_CUST_I.Trim();
            if (!string.IsNullOrEmpty(searchObj.USER_ID_APP_CRTD))
                app_creator_id = searchObj.USER_ID_APP_CRTD.Trim();




            using (var context = new VASTEntities())
            {
                var query = from p in context.APLCN_TRCKR

                            where
                            p.CUST_NAME_X.Contains(cust_name_x) &&
                            p.CUST_SURNM_X.Contains(surname_x) &&
                            p.QUEUE_ID.Contains(queue_id) &&
                            p.BPID.Contains(bp_id) &&
                            p.CURR_OWNR_USER_ID.Contains(user_id) &&
                            p.APLCN_TRCKR_ID.Contains(aplcn_trckr_id) &&
                            p.APLCN_STTS_ID.Contains(aplcn_stts_id) &&
                            p.CUST_ID.Contains(cust_id) &&
                            p.PRCES_TYPE_ID.Contains(process_type) &&
                            p.CELL_ID.Contains(cell_id) &&
                            SqlFunctions.StringConvert((double)p.CR_REGION_ID).Contains(cr_region) &&
                            p.ORIGN_ID.Contains(Origin) &&
                            SqlFunctions.StringConvert((double)p.RGION_ID).Contains(region) &&
                            p.ORGTN_CHANL_ID.Contains(channel) &&
                            p.CR_DCSN_ID.Contains(credit_verdict) &&
                            p.SBSA_CUST_I.Contains(sbsa_cust_id) 

                            select p;

                query.ToList();
                if (query.Count() == 0)
                {
                    throw new Exception("No Applications Found.");
                }
                foreach (var a in query)
                {
                    searchAppsObj.Add(Translator.TranslateReqObjToBO.TranslateDTOToSearchApp(a));
                }
                context.Connection.Close();
                return searchAppsObj;

            }

Shorpy is your best bet

Chin. Udara
– Chin. Udara

2025-04-08 07:02:29 +00:00
Commented Apr 8 at 7:02 — Chin. Udara
– Chin. Udara, Commented Apr 8 at 7:02

D3vtr0n · Accepted Answer · 2022-03-21 06:05:39Z

5

You can just create a collection of lambda expression like below:

var filters = new List<Expression<Func<Application, bool>>>();

if (!string.IsNullOrWhitespace(searchObj.CUST_NAME_X))
    filters.Add(application => application .CUST_NAME_X.Contains(searchObj.CUST_NAME_X.Trim());

if (!string.IsNullOrEmpty(searchObj.SURNM_X))
    filters.Add(application => application .CUST_SURNM_X.Contains(searchObj.SURNM_X.Trim());

// And so on for all criteria

After that you can do a loop on filters like below:

using (var context = new VASTEntities())
{
    var query = context.APLCN_TRCKR;
    foreach(var filter in filters)
    {
        query = query.Where(filter);
    }

    var result = query.ToList();
    if (result.Count() == 0)
    {
        throw new Exception("No Applications Found.");
    }
    foreach (var a in result)
    {
        searchAppsObj.Add(Translator.TranslateReqObjToBO.TranslateDTOToSearchApp(a));
    }
    context.Connection.Close();
    return searchAppsObj;
}

edited Mar 21, 2022 at 6:05

D3vtr0n

2,9393 gold badges34 silver badges55 bronze badges

answered Jun 12, 2018 at 9:10

CodeNotFound

23.4k10 gold badges75 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

siboniso zungu Over a year ago

Hi Thank you for your response, what is application here: filters.Add(application => application .CUST_SURNM_X.Contains(searchObj.SURNM_X.Trim());

CodeNotFound Over a year ago

@sibonisozungu application is just a parameter of a type in your dbset context.APLCN_TRCKR. You can rename it if you want it is just a parameter name. The only thing to take care here if var filters = new List<Expression<Func<Application, bool>>(); you need to replace Application with the correct type used in context.APLCN_TRCKR.

siboniso zungu Over a year ago

Thank you, I have replaced Application with the Table name, and now I get this error: Severity Code Description Project File Line Suppression State Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<VASTRestService.APLCN_TRCKR>' to 'System.Data.Objects.ObjectSet<VASTRestService.APLCN_TRCKR>'. An explicit conversion exists (are you missing a cast?)

siboniso zungu Over a year ago

it worked Thank you @CodeNotFound I had to change the query type: IQueryable<APLCN_TRCKR> query = context.APLCN_TRCKR;

momo · Accepted Answer · 2018-06-12 09:12:23Z

0

change

var query = from p in context.APLCN_TRCKR select p;

to

var query = context.APLCN_TRCKR.AsQueryable();

And when the filtering work is done:

await query.ToListAsync() // this one goes to the database

Also have a look at this: Linq query filtering

In Addition: don't call context.Connection.Close(); as this is going to be executed anyway because using behaves like try { ... } finally { // dispose work }

answered Jun 12, 2018 at 9:12

momo

3,5812 gold badges24 silver badges38 bronze badges

Collectives™ on Stack Overflow

Dynamic Search Using Entity Framework

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related