CODEWITHSUNDEEP
c#sqllinq-to-sql
Sealer_05
Sealer_05

Reputation: 5566

Trim field in linq2sql query without breaking index

I am joining on the zip codes from 2 different tables that have a tons of data and are indexed on zip code.

The join works if I trim off the digits on t.zipplus4 so both zip code fields are only 5 digits.
Unfortunately using substring or replace break the indexing so the query takes too long.
I obviously cant use trimstart? bc it doesnt exist in sql.
In sql left([Zip Code], 5) works just fine with the index.
Is there any way in linq I can pull this off?

example:

  var query = (from t in db.test
             join g in db.test2 on t.zipplus4 equals g.zip
             select t).ToList();

Upvotes: 0

Views: 139

Answers (2)

Sealer_05
Sealer_05

Reputation: 5566

It was not possible to do the trim in linq without breaking the index. The answer was to ditch linq2sql for ado.net so you get all sql functionality.

Upvotes: 0

Oleks
Oleks

Reputation: 32323

The closest equivalent for LEFT in c# is a String.Substring method, which translates into tsql's SUBSTRING function which is not a choice for you.

If you just want to trim unnecessary spaces, use String.Trim method, it will be converted into something like LTRIM(RTRIM([t0].[zipplus4])).

You may try to use tsql's LIKE (it's not clear from the question if LIKE is suitable here) by using SqlMethods.Like:

var query = (from t in db.test
             from g in db.test2
             where SqlMethods.Like(t.zipplus4, g.zip + '%')
             select t).ToList();

But I'm not sure if this breaks the indexes.

If all this is not an option for you, consider creating a stored procedure for this.

Upvotes: 1

Related Questions