Need Help for Join tables in Linq on ASP.NET Core

basically i am working on ASP.NET Core and I have an FromSqlRaw statement which filters and keeps some ids in a value of a talbe (ActiveCalls).
Then with Linq I filter another table of database (AppUsers).
My goal is to keep all columns of Appusers with Ids i take from ActiveCalls.

Code for SqlRaw with Ids result : (13) Correct!

var results = _context.ActiveCalls.FromSqlRaw("SELECT web_userid from ActiveCalls where TimeReceived>'2021-01-01' and GeoX > 23.8097593342164 and GeoX < 23.8838031312548 and GeoY > 38.1026672246045 and GeoY < 38.1607577524088 group by web_userid");

Code for Linq with data :Result (2281) Correct!

appusers = appusers.Where(s => s.LastCall >= start && s.LastCall <= end);

Code for Join tables to keep only Records of AppUsers based on Ids i found on ActiveCalls. Result(10) Wrong!

 appusers = from res in results
                     join ap in appusers
                     on res.web_userid equals ap.Id
                     select ap;

I have to take the result with Linq and those 3 steps and not merge all in on Sql query

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I don’t get an idea of using FromSqlRaw in this case.

var results = 
   from ac in _context.ActiveCalls 
   where ac.TimeReceived > DateTime.Parse("2021-01-01") 
     && ac.GeoX > 23.8097593342164 
     && ac.GeoX < 23.8838031312548 
     && ac.GeoY > 38.1026672246045 
     && ac.GeoY < 38.1607577524088
   select ac.web_userid;

results = results.Distinct();

var appusers = _context.AppUsers.AsQueryable();
appusers = appusers.Where(s => s.LastCall >= start && s.LastCall <= end);

appusers = 
   from res in results
   join ap in appusers on res.web_userid equals ap.Id
   select ap;

If you still does not have correct result, it means that AppUser.Id is not web_userid

Method 2

Try Somthing like this

var results = _context.ActiveCalls.FromSqlRaw("SELECT web_userid from ActiveCalls where TimeReceived>'2021-01-01' and GeoX >
23.8097593342164 and GeoX < 23.8838031312548 and GeoY > 38.1026672246045 and GeoY < 38.1607577524088 group by web_userid");

appusers = appusers.Where(s => s.LastCall >= start && s.LastCall <= end && results.web_userid.contains(s.Id));

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating