Implement search functionality with Entity Framework

Question

I have three tables

SalesDetails with columns SalesId, ProductId, Qty, Price etc
SalesPersonDtls with columns SalesId, SalesPersonId, CommPercentage etc
SalesPerson with columns SalesPersonId, firstName, lastName etc

I have second table because one sale can be done by more than one sales person together with split commission.

I have various inputs in search screen like productname, sales date, sales person name etc.

I am making the model class as 'AsQueryable' and add various where conditions and finally the result into a list.

I have sales person's name in search criteria but I don't know how to include this into the search. Can you please help?

Thanks Peter

First, I would copy your actual models as code snippets instead of a hard to read paragraph. Then show your current query. Taking a blind shot and assuming you have navigation properties defined, I would say your probably looking for something like var matches = context.SalesDetails.Select(** Project Your Needed Data Here **).Where(sd => sd.SalesPersons.Any(sp => sp.lastName == "Jones" && sp.firstName == "Peter").ToList(). — Steve Greene
– Steve Greene, Commented Oct 11, 2017 at 21:26

Ali Afsahnoudeh · Accepted Answer · 2017-10-12 18:02:59Z

Peter
If I get it correct , relation of your business models is like this :

person (n) <-----> (1) Sale (1) <-----> (n) Details
you put sale and person relation in "SalesPersonDtls" and sale and detail relation to "SalesDetails". I think it's better to change your entities a little bit, if you want to get better result as your project getting bigger and more complex.
Your entities should be like this :

    Sale
    {
        List<SalesDetail> details;
        List<Person> persons;
        ...
    }
    SalesDetail
    {
       Sale
       ...
    }
    Person
    {
       Sale
       name
       ...
    }

Now it's really simple , if you want sales that is related to a personName :

sales.Where(sale => sale.Persons.Any(person => person.PersonName == "your input name"));

UPDATE : If you can't or don't want to change your models: first you need to find personId by it'name and then search into your "SalesPersonDtls" and get saleIds.

Collectives™ on Stack Overflow

Implement search functionality with Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related