locked
Query Database Using EF RRS feed

  • Question

  • Hello guys,

    I have two challenges which are quite similar...

    First, Please, i am stuck on writing some complex query using EF. I am able to get this on SQL server, but i need it converted to EF way.

    I have three(3) tables; users, user_detail and department. This is a many-to-many relationship. So, i used the user_detail as junction to the two tables.

    select * from department join user_detail on
    user_detail.department_id = department.department_id join users on user_detail.user_id = users.user_id
    where users.user_id = 4

    Secondly, i noticed that EF did not import user_detail table as i could see . Though based on the EDMX diagram, it shows that the relationship between users and department is man-to-many. But i cannot see the user_detail table.

    Below is a screenshot

    

    I will sincerely appreciate any help on this.

    Saturday, October 13, 2018 10:14 AM

Answers

  • Hi Godymn,

    You could create Data Transfer Objects, such as departmentDto. like this:

     public class DepartmentDto
    {
            public int department_id  { get; set; }
            public int user_id { get; set; }
            public string firstname { get; set; }
            public string lastname{ get; set; }
            public string AuthorName { get; set; }
            public string Genre { get; set; }
    }
    

    Then you could modify the code like this:

    using (var db = new NewEFDemoEntities())
    {
        var result = db.departments.Where(t => t.users.Any(x => x.user_id == 4)).Select(t => new DepartmentDto {
    
                        department_id = t.department_id,
                        department_name = t.department_name,
                        user_id = t.users.Where(a=>a.user_id == 4).FirstOrDefault().user_id,
                        firstname = t.users.Where(a => a.user_id == 4).FirstOrDefault().firstname,
                        lastname = t.users.Where(a => a.user_id == 4).FirstOrDefault().lastname
                    }).ToList();                
    }

    For more information about Create Data Transfer Objects (DTOs), please refer to:

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Godymn Wednesday, October 17, 2018 12:18 PM
    Wednesday, October 17, 2018 8:58 AM

All replies

  • var query =
        from dept in context.department 
    where ( from requiredId in Users where ( from user in dept.Users where user.Id == requiredId select user ).Any() == false select requiredId ).Any() == false select dept;
    • Proposed as answer by Eng.salim Wednesday, October 17, 2018 11:48 AM
    Saturday, October 13, 2018 7:28 PM
  • One would assume that you selected the missing table to be on the EF designer.

    Saturday, October 13, 2018 11:16 PM
  • One would assume that you selected the missing table to be on the EF designer.

    I have tried several times and made sure i didnt take it out. But still the same issue.

    I have also had to re-install EF on my machine. But still same. 

    Don't know what else to do. 

    Sunday, October 14, 2018 1:25 AM
  • Thanks.

    But this query looks alittle bit confusing. Please, can you explain, expecially from the requiredId part.

    Appreciate your feedback.

    Sunday, October 14, 2018 1:53 AM
  • One would assume that you selected the missing table to be on the EF designer.

    I have tried several times and made sure i didnt take it out. But still the same issue.

    I have also had to re-install EF on my machine. But still same. 

    Don't know what else to do. 

    I like to use Visual Stuido project folder and install EF into the folder that I call Model in order to get code seperation with the reset of the code in the project.

    What version of EF is this, and I am assuming you are using DB first approach?


    • Edited by DA924x Sunday, October 14, 2018 5:16 AM
    Sunday, October 14, 2018 5:15 AM
  • One would assume that you selected the missing table to be on the EF designer.

    I have tried several times and made sure i didnt take it out. But still the same issue.

    I have also had to re-install EF on my machine. But still same. 

    Don't know what else to do. 

    I like to use Visual Stuido project folder and install EF into the folder that I call Model in order to get code seperation with the reset of the code in the project.

    What version of EF is this, and I am assuming you are using DB first approach?


    Yes, same with me. I actually created a folder to dumb my model inside it.

    Using V6.2.0 and yes, i am using DB first approach

    Sunday, October 14, 2018 6:12 AM
  • what is the MS SQL Server table schema for this table?
    Sunday, October 14, 2018 8:04 AM
  • Hi Godymn,

    Based on your requirement, please refer to the following linq statement. 

    using (var db = new NewEFDemoEntities())
    {
        var result = db.departments.Where(t => t.users.Any(x => x.user_id == 4)).Select(t => new {
    
                        department_id = t.department_id,
                        department_name = t.department_name,
                        user_id = t.users.Where(a=>a.user_id == 4).FirstOrDefault().user_id,
                        firstname = t.users.Where(a => a.user_id == 4).FirstOrDefault().firstname,
                        lastname = t.users.Where(a => a.user_id == 4).FirstOrDefault().lastname
                    }).ToList();                
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 15, 2018 7:26 AM
  • Hi Godymn,

    Based on your requirement, please refer to the following linq statement. 

    using (var db = new NewEFDemoEntities())
    {
        var result = db.departments.Where(t => t.users.Any(x => x.user_id == 4)).Select(t => new {
    
                        department_id = t.department_id,
                        department_name = t.department_name,
                        user_id = t.users.Where(a=>a.user_id == 4).FirstOrDefault().user_id,
                        firstname = t.users.Where(a => a.user_id == 4).FirstOrDefault().firstname,
                        lastname = t.users.Where(a => a.user_id == 4).FirstOrDefault().lastname
                    }).ToList();                
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hello Zhanglong,

    Thanks for the reply. I will check this and give you a feedback soon when i get on my workstation.  Thanks for the reply.

    Wednesday, October 17, 2018 8:46 AM
  • Hi Godymn,

    Based on your requirement, please refer to the following linq statement. 

    using (var db = new NewEFDemoEntities())
    {
        var result = db.departments.Where(t => t.users.Any(x => x.user_id == 4)).Select(t => new {
    
                        department_id = t.department_id,
                        department_name = t.department_name,
                        user_id = t.users.Where(a=>a.user_id == 4).FirstOrDefault().user_id,
                        firstname = t.users.Where(a => a.user_id == 4).FirstOrDefault().firstname,
                        lastname = t.users.Where(a => a.user_id == 4).FirstOrDefault().lastname
                    }).ToList();                
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hello Zhanglong,

    Thanks for the reply. I will check this and give you a feedback soon when i get on my workstation.  Thanks for the reply.

    Hi Zhanglong,

    I just tried the query now, but i cannot return the anonymous object type which i intend to use in another function. Please, can you assist to show me how its done?

    Appreciate your feedback.

    Wednesday, October 17, 2018 8:51 AM
  • Hi Godymn,

    You could create Data Transfer Objects, such as departmentDto. like this:

     public class DepartmentDto
    {
            public int department_id  { get; set; }
            public int user_id { get; set; }
            public string firstname { get; set; }
            public string lastname{ get; set; }
            public string AuthorName { get; set; }
            public string Genre { get; set; }
    }
    

    Then you could modify the code like this:

    using (var db = new NewEFDemoEntities())
    {
        var result = db.departments.Where(t => t.users.Any(x => x.user_id == 4)).Select(t => new DepartmentDto {
    
                        department_id = t.department_id,
                        department_name = t.department_name,
                        user_id = t.users.Where(a=>a.user_id == 4).FirstOrDefault().user_id,
                        firstname = t.users.Where(a => a.user_id == 4).FirstOrDefault().firstname,
                        lastname = t.users.Where(a => a.user_id == 4).FirstOrDefault().lastname
                    }).ToList();                
    }

    For more information about Create Data Transfer Objects (DTOs), please refer to:

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Godymn Wednesday, October 17, 2018 12:18 PM
    Wednesday, October 17, 2018 8:58 AM
  • Use This

    var userIds = new int[]{1, 3, 7, 23, 56};

    var query =from dept in context.department where

    dept.Users.All(user => userIds.Contains(user.userId))select dept;


    Wednesday, October 17, 2018 11:59 AM
  • Hi Godymn,

    You could create Data Transfer Objects, such as departmentDto. like this:

     public class DepartmentDto
    {
            public int department_id  { get; set; }
            public int user_id { get; set; }
            public string firstname { get; set; }
            public string lastname{ get; set; }
            public string AuthorName { get; set; }
            public string Genre { get; set; }
    }

    Then you could modify the code like this:

    using (var db = new NewEFDemoEntities())
    {
        var result = db.departments.Where(t => t.users.Any(x => x.user_id == 4)).Select(t => new DepartmentDto {
    
                        department_id = t.department_id,
                        department_name = t.department_name,
                        user_id = t.users.Where(a=>a.user_id == 4).FirstOrDefault().user_id,
                        firstname = t.users.Where(a => a.user_id == 4).FirstOrDefault().firstname,
                        lastname = t.users.Where(a => a.user_id == 4).FirstOrDefault().lastname
                    }).ToList();                
    }

    For more information about Create Data Transfer Objects (DTOs), please refer to:

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Zhanglong,

    Because you have taken your time to give me an idea on how to go about this, I will mark this as an answer. Your idea worked for me and easy to understand.

    Though, I am a little bit stuck on the last part; how to load the data gotten from the query to the datagridview object, using the data transfer object class. It shows blank when I bind the DTo  class to the datagridview.

    Any idea on how to go about this last step will be well appreciated.

    Thanks for the help so far.

    Wednesday, October 17, 2018 12:18 PM
  • Hi Godymn,

    You could create Data Transfer Objects, such as departmentDto. like this:

     public class DepartmentDto
    {
            public int department_id  { get; set; }
            public int user_id { get; set; }
            public string firstname { get; set; }
            public string lastname{ get; set; }
            public string AuthorName { get; set; }
            public string Genre { get; set; }
    }

    Then you could modify the code like this:

    using (var db = new NewEFDemoEntities())
    {
        var result = db.departments.Where(t => t.users.Any(x => x.user_id == 4)).Select(t => new DepartmentDto {
    
                        department_id = t.department_id,
                        department_name = t.department_name,
                        user_id = t.users.Where(a=>a.user_id == 4).FirstOrDefault().user_id,
                        firstname = t.users.Where(a => a.user_id == 4).FirstOrDefault().firstname,
                        lastname = t.users.Where(a => a.user_id == 4).FirstOrDefault().lastname
                    }).ToList();                
    }

    For more information about Create Data Transfer Objects (DTOs), please refer to:

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Zhanglong,

    Because you have taken your time to give me an idea on how to go about this, I will mark this as an answer. Your idea worked for me and easy to understand.

    Though, I am a little bit stuck on the last part; how to load the data gotten from the query to the datagridview object, using the data transfer object class. It shows blank when I bind the DTo  class to the datagridview.

    Any idea on how to go about this last step will be well appreciated.

    Thanks for the help so far.

    Hi Zhanglong,

    I just figured out how to load it now. Thanks a lot for the help. Your simply the best!

    Much appreciated.

    Wednesday, October 17, 2018 12:40 PM