1

I'm trying to apply join in EF Core, but it's returning wrong data.

If I run the below query in SQL Server it returns no results, its expected behavior.

select * from device d
inner join  store s on s.Id = d.StoreId
inner join category c on c.Id = d.CategoryId
where s.StoreName like '%dsfd%' and c.CategoryName like '%asdasdasd1%'

But When I try to run the similar code from the C# application for the same input values, it returns one row.

var serachresult = (from devices in _deviceRepository.GetAll()
join stores in _storeRepository.GetAll() on devices.StoreId equals stores.Id
join categories in _categoryRepository.GetAll() on devices.CategoryId equals categories.Id 
where

(!string.IsNullOrWhiteSpace(searchInput.StoreName) ? stores.StoreName.Contains(searchInput.StoreName) : true) &&

(!string.IsNullOrWhiteSpace(searchInput.CategoryName) ? categories.CategoryName.Contains(searchInput.CategoryName) : true) 

select new DeviceDetailsDto

{
    Id = devices.Id,
    DeviceName = devices.DeviceName,
    DeviceDesc = devices.DeviceDesc,
    DeviceCode = devices.DeviceCode,
    StoreId = devices.StoreId,
    CategoryId = devices.CategoryId,
    CategoryName = categories.CategoryName,
    StoreName = stores.StoreName
});

Returned record

Id, AssetID, CategoryId, CreationTime, CreatorUserId, DeleterUserId, DeletionTime, DeviceCode, DeviceDesc, DeviceName, FlgActive, IsDeleted, LastModificationTime, LastModifierUserId, StoreId, Id, AddressLine1, AddressLine2, City, Country, CreationTime, CreatorUserId, DeleterUserId, DeletionTime, EmailAddress, FlgActive, IsDeleted, LastModificationTime, LastModifierUserId, PhoneNo, PostalCode, State, StoreDesc, StoreName, StoreNo, Id, CategoryDesc, CategoryName, CreationTime, CreatorUserId, DeleterUserId, DeletionTime, FlgActive, ImageName, ImagePath, IsDeleted, IsLast, LastModificationTime, LastModifierUserId, Level, ParentId, StoreId, TemplatesTypesId '3', '123123123', '21', '2018-05-14 18:02:58.480972', '2', NULL, NULL, 'asdasd', 'asdasd', 'asdasd', '1', '0', NULL, NULL, '1', '1', 'asdasdasd', 'asdasd', 'dasasd', 'sdasd', '2018-05-09 17:14:47.141586', '1', '0', '2018-05-10 11:48:34.897152', 'dasdasd@gmail.com', '0', '0', NULL, NULL, 'asdsadsa', 'dasdasd', 'asdas', 'dsfdsf', 'dsfd', 'fdsdf', '21', 'AASAS', 'asS', '2018-05-10 11:30:05.237959', '1', NULL, NULL, '0', 'img2.jpg', 'C:\workspace\src\Nec.Spar.Web.Host\img2.jpg', '0', '1', NULL, NULL, '0', '0', '1', NULL

Update:

Returned record with less columns

Id DeviceCode DeviceDesc DeviceName StoreId StoreName CategoryId CategoryName
3 asdasd 'asdasd' 'asdasd' '1' dsfd '21' 'asS'
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197
  • 1
    Try to use .ToLower(). `stores.StoreName.ToLower().Contains(searchInput.StoreName.ToLower())` and `categories.CategoryName.ToLower().Contains(searchInput.CategoryName.ToLower())`. Even if this works: you might have a "Turkey issue" http://www.moserware.com/2008/02/does-your-code-pass-turkey-test.html – G43beli May 15 '18 at 07:06
  • @G43beli But how `.ToLower().` is going to help in this case? There is no math between asdasdasd1 and asS – Vivek Nuna May 15 '18 at 07:22
  • Let EF print your query. Either `searchInput.StoreName` or `searchInput.CategoryName` does not contain what you expect it to. – CodeCaster May 15 '18 at 07:25
  • @CodeCaster How to print query? Could you please explain your comment, its not clear to me – Vivek Nuna May 15 '18 at 07:27
  • https://stackoverflow.com/a/20751723/ – CodeCaster May 15 '18 at 07:54
  • What EF Core version are you on? – Ivan Stoev May 15 '18 at 08:28
  • @IvanStoev EF Core version 2.0.1 – Vivek Nuna May 15 '18 at 09:13
  • You can add debug logging of all EF generated SQL by adding a LoggerFactory when registering the DbContext: In Startup.cs: public static readonly LoggerFactory DebugLoggerFactory = new LoggerFactory(new[] { new Microsoft.Extensions.Logging.Debug.DebugLoggerProvider() }); In ConfigureServices: services.AddDbContext(options => options.UseSqlServer(Configuration.GetConnectionString("MyConnectionString")) .UseLoggerFactory(DebugLoggerFactory)); Generated SQL will be shown in the debug output in VS. – Peter May 15 '18 at 11:50
  • @viveknuna I'm afraid this is not reproducible. If you provide repro, I can take a look. Without that you are the only one who can figure out what is going on. – Ivan Stoev May 15 '18 at 17:42
  • @IvanStoev I’m also tried IndexOf instead of Contains. It works as expected. Is this turkey issue as mentioned by G43beli in the first comment. – Vivek Nuna May 15 '18 at 17:48
  • 1
    But `string.IndexOf` is not translated to SQL and is evaluated locally. Did you try `EF.Functions.Like`? – Ivan Stoev May 15 '18 at 18:11
  • @IvanStoev I haven’t try ‘EF.Functions.Like’. How to use it? – Vivek Nuna May 15 '18 at 18:13
  • Similar to SQL, e.g. instead of `stores.StoreName.Contains(searchInput.StoreName)`, try `EF.Functions.Like(stores.StoreName, "%" + searchInput.StoreName + "%")`. It's explained (sort of) [here](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.0#effunctionslike). – Ivan Stoev May 15 '18 at 18:16
  • Thanks @IvanStoev I’ll try and let you know. – Vivek Nuna May 15 '18 at 18:18
  • Any update on that? Also, it would help to see generated SQL. – Gert Arnold Jan 29 '23 at 14:39

0 Answers0