Replacing old sql written in asp.net with Linq
I was trying to replace the LINQ SQL to switch to MVC, but I have no idea how to use a subquery in LINQ. Now I have replaced the inner SQL query with LINQ, I would like to know how to make the outer query.
Below is the SQL query
SELECT
DISTINCT GP_REGION.REGION_MAIN Region_Code,
R1.REGION_NAME
FROM
GP_REGION
INNER JOIN GP_REGION R1 ON GP_REGION.REGION_MAIN = R1.REGION_CODE
WHERE
GP_REGION.REGION_HAS_DATA = 'Y'
AND
GP_REGION.REGION_MAIN IN
(
SELECT
DISTINCT AR.BRANCH_CODE
FROM
PORTAL.UA_APPLN_ROLE AR
INNER JOIN PORTAL.UA_GROUP G ON AR.GROUP_CODE = G.GROUP_CODE
WHERE
G.USER_ID = '" + Global.UserId() + "' AND AR.APPLICATION_ID = '" + ApplicationId + "'
)
ORDER BY
GP_REGION.REGION_MAIN
Here the inner query I have replaced as shown below using the following LINQ
var regions = from p in db3.UA_APPLN_ROLE.AsEnumerable()
join i in db3.UA_GROUP.AsEnumerable()
on p.GROUP_CODE equals i.GROUP_CODE
where
i.USER_ID == Global.UserId()
&&
p.APPLICATION_ID == ConfigurationManager.AppSettings["ApplicationId"]
select new
{
branchcode = p.BRANCH_CODE
};
But I would like to know the external sql query, how can I replace and join the existing LINQ code that I wrote
source to share
You concatenate the subquery into the main query like this:
var efQuery = from gp in db3.GP_REGION
join r1 in db3.GP_REGION on gp.REGION_MAIN equals r1.REGION_CODE
where regions.Contains(gp.REGION_MAIN)
&& gp.REGION_HAS_DATA = "Y"
select new
{
Region_Code = gp.REGION_MAIN,
Region_Name = r1.REGION_NAME
};
var queryResult = efQuery.Distinct().OrderBy(x => x.Region_Code);
You can speed up the processing a bit by using .AsNoTracking()
, for example. db3.GP_REGION.AsNoTracking()
...
In general, it is a good idea to control the generated SQL to avoid situations where it looks simple in the code but converts to bad SQL.
But in my opinion, the benefits of having a first class member query in your code (versus SQL in strings) outweigh the sometimes unintuitive generated SQL.
source to share