PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
How to perform this SQL query in Code First?-VBForums
Results 1 to 8 of 8

Thread: How to perform this SQL query in Code First?

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2018
    Posts
    42

    How to perform this SQL query in Code First?

    Hi everyone,

    I come here with a doubt related with Entity Framework Code First and SQL.

    I have a table called "Company" that stores the information related with the companies. In other table called CompanyNames I store the n names that every company can use (so there is a 1-N relation between Company and CompanyNames.

    I can get the results of CompanyName in this way:

    Code:
    using (var contxt = new MyDatabaseContext())
                {
                    var query = from b in contxt.CompanyNames where b.Name.Equals("Happy Company Inc.") select b;
                    var Registers = query.ToList();
    
                    for (int Cont = 1; Cont <= Registers.Count(); Cont++)
                    {
                        MessageBox.Show(Registers[Cont - 1].CompanyId);
                    }
                }
    In this way I access to all the CompanyId that a name point (usually it will be one, but can be more than one if two companies share a Name/alias.

    But I don't need those CompanyId's, I want to get the Companies itself, so it would be someting like:

    Select * From Companies where CompanyId in (Select CompanyId from CompanyNames where Name="VB Forums Is Great Inc")
    Could someone tell me how this query would be using Code First?? Once I know it will help me to perform other similar ones

    Many thanks!

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,444

    Re: How to perform this SQL query in Code First?

    csharp Code:
    1. var companies = (from c in context.Companies
    2.                  where c.CompanyNames.Any(cn => cn.Name == "Happy Company Inc.")
    3.                  select c).ToArray();

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,369

    Re: How to perform this SQL query in Code First?

    You're looking for a JOIN. In SQL it looks a bit like this:-
    Code:
    Select *
    From Company C
    Join CompanyNames CN
       on C.CompanyID = CN.CompanyID
    Where CN.Name = 'Happy Company Inc.'

    Entity Frameworks syntax is very similar


    Edit> Crossed over with JM

    Note that JM has gone for a literal translation of an In list by using an Any. Mine is a Join. I believe they will produce the same result in your case but be aware that the result could be subtly different if a single company could have the same name more than once. If that happened mine would return two rows and JM's would return just one.

    I think mine will perform slightly better as it's the form that sql server is better at consuming but I'm not sure how Entity Framework will translate it into actual SQL so try both ways and pick the best.
    Last edited by FunkyDexter; May 20th, 2019 at 07:42 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,444

    Re: How to perform this SQL query in Code First?

    Personally, I tend to use function syntax almost exclusively, which would look like this:
    csharp Code:
    1. var companies = context.Companies
    2.                        .Where(c.CompanyNames.Any(cn => cn.Name == "Happy Company Inc."))
    3.                        .ToArray();

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,369

    Re: How to perform this SQL query in Code First?

    Have you checked what SQL it emits? I'm curious as to whether it
    1. translates it to an In query (basically what the OP posted)
    2. resolves the inner query and then passes the result as a csv to the outer query
    3. resolves both separately and then merges them clientside.

    I'd hope it's option 1 but Entity Framework was notoriously bad at this sort of stuff prior to core.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    Member
    Join Date
    Jul 2018
    Posts
    42

    Re: How to perform this SQL query in Code First?

    Many thanks for so many answers!!!

    To he honest, I learnt SQL almost 20 years ago and later I used it only in a few simple proyects of web applications using MySQL.

    As far as I remenber, Join was completely un-adviced due to the performante and resources impact it had (implyes join two big tables everytime it's called).

    I'm going to start trying the second solution jmcilhinney has reccomend, and then I'll try others.

    In this point I'm going to have to develop the part of the program that takes the data stored on excel tables and import into the database, so I have real data to test (if not, it's impossible to test execution times and compare different queries to take the faster one).

    Regards! I'll back to tell my results
    Last edited by TassadarNET; May 20th, 2019 at 09:31 AM.

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    2

    Re: How to perform this SQL query in Code First?

    best of lucks!

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,369

    Re: How to perform this SQL query in Code First?

    Join was completely un-adviced due to the performante and resources impact it had (implyes join two big tables everytime it's called).
    Oooh, that's BAD advice you've been given there. A join is one of the most basic elements of sql syntax and database engines are optimised to consume them. I try to avoid saying "always" but a join is almost always the most performant way of joining two datasets together. If you were querying directly into the database then I would 100% recommend using a Join rather than an In and it's only the fact that you're going via entity framework that muddies the water for me.

    That said, the "In" approach that you originally posted and which JM has given you the entity framework equivalent of will probably give you "good enough" performance so if you're more comfortable with the syntax then go with it. But don't avoid joins because you've been told they're bad practice. They're good practice and absolutely should be your starting point.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width