0

So I have this query on MSSQL where BANKS is a Table View

SELECT t.*
  FROM [DB].[dbo].[BANKS] t
  where t.BCODE = 'xxxxxx    '; <--- with spaces

which above query has 1 result BCODE : "xxxxxx" <--- no space

on converting it to entity framework using ToList() or as IEnumerable(); e.g.

var _BANKS = dbcontext.BANKS.IEnumerable();
//var _BANKS = dbcontext.BANKS.ToList();

Just note that I have to put this on a Memory because I constantly refer to this table as reference.

On simple execution

string bankcode  = "xxxxxx    ".Trim();
var test = _BANKS.Where(q => q.BCODE == bankcode ).ToList(); // <--- would return me null

var test2 = from t in _BANKS where t.BCODE == "xxxxxx" select new t; <--- still null

but when I change the _BANKS as AsQueryable(); using the same code snippet above, it would give the desired result the same on the native query (see first SQL snippet).

I'm avoiding the .AsQueryable() because it will give me a runtime error specifically "The specified LINQ expression contains references to queries that are associated with different contexts." because I'm using it to constantly refer in a different DBContext's.

Julius Limson
  • 526
  • 9
  • 29
  • 2
    It's not weird, it's how SQL server works: https://stackoverflow.com/questions/17876478/why-the-sql-server-ignore-the-empty-space-at-the-end-automatically The lookup on the memory array does not exhibit this behaviour, which is why your lookup fails. You can always do `_BANKS.Where(q => q.BCODE?.Trim() == bankcode )` to workaround this. – zaitsman Jul 28 '20 at 05:09
  • Thanks for your response your link is so helpful @zaitsman but the workaround `q.BCODE?.Trim()` did not work as well. I also did a lot of trimming before I made this post. – Julius Limson Jul 28 '20 at 05:20
  • I assume that `[BCODE]` is defined as a `char` datatype in SQL? – Jason Jul 28 '20 at 05:29
  • @Jason it's a VARCHAR(6, null) and the `bankcode` is form a VARCHAR(12, null) if it matters. – Julius Limson Jul 28 '20 at 05:32
  • Ok ... question. Why are you using `dbcontext.BANKS.IEnumerable()` instead of simply `dbcontext.BANKS` without `ToList()` or `ToEnumerable()`? – Jason Jul 28 '20 at 05:38
  • hi @Jason the same reason why I'm avoiding the `AsQueryable()` thanks for your help. – Julius Limson Jul 28 '20 at 05:47
  • Did you check how the result values appear in your list? If they are there with spaces, then you should search with spaces.. – Gert-Jan M Jul 28 '20 at 05:52
  • I get the impression that not all the information required to solve this is in the question you posted. The error `"The specified LINQ expression contains references to queries that are associated with different contexts"` usually occurs when you combine two separate LINQ queries from two separate `DBContext` objects. But that shouldn't prevent you from using `AsQueryable()` on one, and calling `ToList()' on the result which you could then use it in the other `DBConext` Query. – Jason Jul 28 '20 at 06:02
  • @Jason you are right, I actually have another `working workaround` for this problem which is slower. but I'm baffled why ToList() and IEnumerable() can't have the same result with a simple `string` query, don't you think?. and should've been a better approach in my case in my opinion. using `Memory` as a recurring reference instead of the DB context itself. – Julius Limson Jul 28 '20 at 06:37

2 Answers2

0

The reason why in case of a simple SQL statement, you got one result is pretty obvious for me: it is that in the database, you have a record which contains spaces, i.e. 'xxxxxx '.

Now why it is not working when using the below code?:

string bankcode  = "xxxxx    ".Trim();
var test = _BANKS.Where(q => q.BCODE == bankcode ).ToList(); // <--- would return me null

var test2 = from t in _BANKS where t.BCODE == "xxxxxx" select new t; <--- still null

Because you are trimming the bankcode which will lead to converting "xxxxx   " to "xxxxx" but in the database, you have an actual record matching this "xxxxx   ". That's why it will return null which means it will not find matching records.

To overcome this issue use LINQ to SQL Contains() method:

string bankcode  = "xxxxx    ".Trim();
var test = _BANKS.Where(q => q.BCODE.Contains(bankcode)).ToList();
Arsen Khachaturyan
  • 7,904
  • 4
  • 42
  • 42
  • Hi, Thank you for your time. I already tried this and weirdly this has the same results. in the BANKS database, it has a varchar(6) so there are no spaces. – Julius Limson Jul 29 '20 at 00:00
  • If it is indeed limited to 6 characters, adding anything after the 6th, like `where t.BCODE = 'xxxxxx '; <--- with spaces`, is pointless and just adds to the confusion. – Andrew Jul 29 '20 at 00:12
  • Hi Andrew. ` 'xxxxxx ';` <--- with spaces i put this because I would like to emphasize that this is from different dbcontext varchar(12) to be exact.( I'm sorry if it made it more confusing.) --- and thus querying it to native SQL would give the expected result. as explained here https://stackoverflow.com/questions/17876478/why-the-sql-server-ignore-the-empty-space-at-the-end-automatically that said I'm aware that tolist and ienumerable does not have the same mechanism that `asqueryable` have. that's why we included a `trimming` in the second snippet. – Julius Limson Jul 29 '20 at 00:50
  • @JuliusLimson I know this might seem a bit trivial, but did you check the database record("xxxxx") to contain only English "x" records? – Arsen Khachaturyan Jul 29 '20 at 08:06
  • @ArsenKhachaturyan hi, i know what you mean. xxx is just an alias to hide real code but yes, i did check. – Julius Limson Jul 29 '20 at 09:21
0

The issue was not in the .ToList() or the .IEnumerable, but rather it is related to a memory leak when you are trying to save the data in the Memory from a Table view.

Workaround

var _BANKS = (from banks in dbcontext.BANKS
             select new {
              banks.BCODE,
              // ... so on.
             }).ToList();

re-selecting the LINQ from storing into your Memory weirdly corrects the inconsistency.

Julius Limson
  • 526
  • 9
  • 29