locked
How to write subquery with alias column in ado.net entity RRS feed

  • Question

  • User1746027085 posted

    this is my raw sql query i want to get and show names of both people who applied for the job and who posted it from Users table. now problem is i can get names of people who applied for job with raw sql query but i don't know how to write it in ado.net entity query below is the working query which is fulfilling my purpose but its in raw sql not in ado.net entity

    select u.Name,u.ID as postedBy,

    AppliedBy=(select name from Users where Users.ID=a.U_ID),j.Job_ID, f.File_ID,f.FileName,f.FilePath,f.U_ID

    from Users u left join Jobs j on u.ID=j.User_ID

    left join Applied_Jobs a on j.Job_ID=a.Job_ID

    left join Files f on a.U_ID=f.U_ID

    where j.User_ID=3 and a.U_ID is not null

    sorry for poorly named alias columns

    Name column is for those who posted jobs

    PostedBy column contains ID of those who posted the job

    AppliedBy are those people who applied for the posted Job

    Job_ID is the Unique ID for each job which is given whenever a user posts a job

    File_ID,FileName and FilePath you can safely ignore that but just in case they are exactly as named

    last is U_ID Column this is the ID of the people who applied for the job

    Friday, January 3, 2020 10:25 AM

Answers

  • User-17257777 posted

    Hi shahzaib17,

    You can refer to the below linq.

    var x = (from u in db.Users
                        join
      j in db.Jobs on u.ID equals j.User_ID
                        join a in db.Applied_Jobs on j.Job_ID equals a.Job_ID
                        join f in db.Files on a.U_ID equals f.U_ID
                        where j.User_ID == 3 && a.U_ID != null
                        select new
                        {
                            Name = u.Name,
                            postedBy = u.ID,
                            AppliedBy = (from uu in db.Users where uu.ID == a.U_ID select uu.Name).FirstOrDefault().ToString(),
                            Job_ID = j.Job_ID,
                            File_ID = f.File_ID,
                            FileName = f.FileName,
                            FilePath = f.FilePath,
                            U_ID = f.U_ID,
                        }).ToList();

    If it doesn't meet your needs, feel free to tell us the problem.

    Best Regards,

    Jiadong Meng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2020 8:56 AM

All replies

  • User281315223 posted

    It's worth noting that you can execute raw SQL queries within Entity Framework using the `Database.SqlQuery()` method:

    var result = context.Database.SqlQuery<string>("...");

    This example would assume that a String is being returned, however SqlQuery accepts a type parameter that can map to a DTO that would contain all of the values that you were looking for. You can see a more fully featured example of this here.

    Friday, January 3, 2020 9:21 PM
  • User1746027085 posted

    i think i didn't make my self clear

    i know how to do it with raw sql with stored procedure but i want to do it with a linq query using entity framework(ORM)

    above line of code will work but i don't want to use raw sql that's the whole point

    Saturday, January 4, 2020 8:26 AM
  • User-17257777 posted

    Hi shahzaib17,

    You can refer to the below linq.

    var x = (from u in db.Users
                        join
      j in db.Jobs on u.ID equals j.User_ID
                        join a in db.Applied_Jobs on j.Job_ID equals a.Job_ID
                        join f in db.Files on a.U_ID equals f.U_ID
                        where j.User_ID == 3 && a.U_ID != null
                        select new
                        {
                            Name = u.Name,
                            postedBy = u.ID,
                            AppliedBy = (from uu in db.Users where uu.ID == a.U_ID select uu.Name).FirstOrDefault().ToString(),
                            Job_ID = j.Job_ID,
                            File_ID = f.File_ID,
                            FileName = f.FileName,
                            FilePath = f.FilePath,
                            U_ID = f.U_ID,
                        }).ToList();

    If it doesn't meet your needs, feel free to tell us the problem.

    Best Regards,

    Jiadong Meng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2020 8:56 AM
  • User1120430333 posted

    If it doesn't meet your needs, feel free to tell us the problem.

    The part about sending the results into an ASP.NET MVC view was left out by the OP in posts made in other forums. One can probably send anaymous types into the MVC view by using expando object or using a dynamic object but not anaymous type. 

    He was also told to use a sproc, call the sproc from EF,  map it into a custom type and be done with it, the simple solution.

    He was shown how to use a Linq projection using a custom type.

    Monday, January 6, 2020 10:27 AM
  • User1746027085 posted

    dude what's your problem?

    why are you being over efficient don't you have any other thing to do?

    you didn't help now you are interfering when some1 else is trying to help

    get a life man..

    Monday, January 6, 2020 12:01 PM
  • User1746027085 posted

    Thank you so much for your time you have solved one of my problem that is writing subquery for alias column

    now is there any way i can populate my MVC app View with this alias column?should i make a virtual property in model class and use it for displaying the alias column values in the View?or is there any other solution for this?

    Monday, January 6, 2020 12:04 PM
  • User1746027085 posted

    there's another issue this above query will only return one row but in my raw sql query its returning multiple rows like it should

    Monday, January 6, 2020 1:15 PM
  • User1746027085 posted

    sorry my mistake your quesry is returning all rows its 100% correct sorry for mistake..thanks alot :-)

    Monday, January 6, 2020 1:59 PM
  • User1120430333 posted

    dude what's your problem?

    why are you being over efficient don't you have any other thing to do?

    you didn't help now you are interfering when some1 else is trying to help

    get a life man..

    Your blatant disrespect attitude you showed in the MSDN C# forum when you went clown and started whining is my pr4oblem with you. 

    Monday, January 6, 2020 2:13 PM