MVC5 EF CodeFirst Select based on statement

0
votes

I have an table in my DB with four columns (priceId, price, orderId, projectId, customerId, categoryId). If i have the categoryId 1 and select all rows with that value.

From that result i want to select in this priority the first one, that has orderId, second that has projectId, third one that has customerId and last one that has empty(null) on customerid, porjectid and orderid.

The table is an price matrix that store prices for my categories and my project, order or customer can had unique prices.

Someone knows how i can do the query in mvc 5 C# with LINQ? Iam stuck on this.

Thanks!

2
c#entity-frameworkasp.net-mvc-5
Can you give an example? And MVC has nothing to do with with Linq btw. - Maarten
I think I understand your first paragraph. You selected everything from your table with a categoryId of 1. I need some clarification on your second paragraph. You say you want to select from the first result in a specific priority? Do you mean you want to order the results of the first query, or are you now making 4 new queries based off of that first query, or am I completely wrong? - Zack
You are almost right. its for new queries based off that first query. And if it has an orderid i want to select the price on that row. And if orderId is empty. I want to check next witch is projectId and if that also is empty i want to check next, customerId and if that also is empty i want the price for row with wmpty order, project and customer. - Kimpa.nu
And you wish to do this all in one statement? - Blackunknown
Not Neccessary. it could be more then one if possible. I want just the correct price based the priroity. - Kimpa.nu

2 Answers

0
votes

You can do this by ordering the results:

var products = from p in Products
                where p.Category == categoryId
                orderby !p.orderId.HasValue, 
                        !p.projectId.HasValue, 
                        !p.customerId.HasValue, 
                        p.Name
                select p.price;

var result = procucts.FirstOrDefault();

The records having a orderId will be on top (false comes before true). If there are none, the ones with projectId will be on top, and so on.

I'm not sure if by "has orderId" you mean has an orderid (i.e. not null) or has a specific orderid. If the latter is true you can change the predicates:

var products = from p in Products
                where p.Category == categoryId
                orderby !p.orderId == orderId, 
                        !p.projectId == projectId, 
                        !p.customerId == customerId, 
                        p.Name
                select p.price;
0
votes

This is not elegant but it should get the job done.

        using (var context = new NameofEntity())
        {
            var result = context.NameofEntityObject
                .Where(a => a.categoryId == 1);

            var result1 = result
                .Where(a => a.orderId != null)
                .FirstOrDefault()
                .price;

            var result2 = result
                .Where(a => a.projectId != null)
                .FirstOrDefault()
                .price;

            var result3 = result
                .Where(a => a.customerId != null)
                .FirstOrDefault()
                .price;

            var result4 = result
                .Where(a => a.orderId == null)
                .Where(a => a.projectId == null)
                .Where(a => a.customerId == null)
                .FirstOrDefault()
                .price;
        }

        if (result1 != null)
            return result1;
        if (result2 != null)
            return result2;
        if (result3 != null)
            return result3;

            return result4;