Compare C# string with Sql Server decimal type

Question

I have a scenario that I have string value in c# and in SQL server the data type is decimal and requirement is that i have to filter value like we do in string which includes (startswith,endswith,contains etc) filters. I am trying like this :

 customers = customers.Where(x => Convert.ToString(x.CrmCustomerNumber).Contains(value));

but it's give me error because you can't use Convert.tostring in IQuerable. I know that I can do that

customers.ToList().Where(x => Convert.ToString(x.CrmCustomerNumber).Contains(value));

but I am doing Customer.ToList() at the end after applying all filters. is there any solution of my problem?

Why do such a comparison at all? Just parse the string value into a decimal and compare with the data. You probably misunderstood the requirement anyway - nobody asks you to use the slowest way to query data. Moste likely they asked you to avoid floating point uncertainty. Using System.Decimal and specifying the correct scale and precision in your SQL Command parameters takes care of that — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 2, 2017 at 11:55
@PanagiotisKanavos,it could be that, or it could really be a task to enable quiries like "give us all customers whose IDs in our CRM start with '123' or whose IDs contain '567'". Looks weird to me, but certainly possible. In which case what OP asks for is a valid thing. — Andrei
– Andrei, Commented May 2, 2017 at 12:00
@Anrei not for startswith. This would still force a full table scan, when a range query would use the primary key. Besides, this would require an integer or bigint, not a decimal. Whatever the real requirement is, it can be handled without resorting to full-table scans — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 2, 2017 at 12:01
@Imranbutt what is your actual problem and why do you think you need the equivalent of LIKE '%123% to do it? There's probably another way to do this — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 2, 2017 at 12:03
@Andrei or it could be a more serious problem, eg a CSV field instead of a many-to-many relation. In this case, redesigning the schema would fix the problem and improve performance — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 2, 2017 at 12:06

Mel · Accepted Answer · 2017-08-30 12:00:09Z

If the numbers are of a known size, for instance if they are all six digits, then you could just query for a value range.

Example: I'm looking for all six-digit numbers that start with 123. That's the same as saying "WHERE Value BETWEEN 123000 AND 123999". So you could query .Where(x => x >= 123000 && x <= 123999).

IF the numbers aren't all a consistent size, but at least have some practical limit, you could extend this to say .Where(x => x == 123 || (x >= 1230 && x <= 1239) || (x >= 12300 && x <= 12399) || (x >=123000 && x <= 123999). etc.

With a little math, you could make this work for any number. (x >= n * 10 && x <= ((n * 10) + 9))

EndsWith can be done using modulo math. Contains... well... you're stuck with a table scan. In that case, you might seriously consider adding a second, perhaps computed column to the table, one that stores the same value as a string, and then indexing that column. Then, use the new column to do the searches.

Collectives™ on Stack Overflow

Compare C# string with Sql Server decimal type

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