INNER JOIN not being generated

2 posts, 1 answers
  1. MotoSV
    MotoSV avatar
    7 posts
    Member since:
    Nov 2011

    Posted 26 Oct 2012 Link to this post

    Hi

    I am using the latest version of OpenAccess and when running the following LINQ query...
    ( from user in domainModel.Users
      join company in domainModel.Companies on user.Id equals company.UserId
      where user.UserName == userName
      select company ).FirstOrDefault()


    ...I get the following SQL statements reported in the SQL Profiler...
    SELECT  TOP(@__TAKE) b.[Id] AS COL1 FROM [User] a JOIN [Company] AS b ON (a.[Id] = b.[UserId]) WHERE a.[UserName] = 'aperson@example.com'
    SELECT [Name] AS COL1, [Synopsis] AS COL2, [UserId] AS COL3, [UserId] AS COL4, [WebsiteUrl] AS COL5 FROM [Company] WHERE [Id] = @p0

    When I run the same query using the Entity Framework I get...
    SELECT TOP (1)
    [Extent2].[Id] AS [Id],
    [Extent2].[UserId] AS [UserId],
    [Extent2].[Name] AS [Name],
    [Extent2].[Synopsis] AS [Synopsis],
    [Extent2].[WebsiteUrl] AS [WebsiteUrl]
    FROM  [dbo].[User] AS [Extent1]
    INNER JOIN [dbo].[Company] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
    WHERE N'aperson@example.com' = [Extent1].[UserName]

    Can anyone tell me why I get two different SQL statements?

    Thanks

    Michael






  2. Answer
    Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 31 Oct 2012 Link to this post

    Hi MIchael,

    This behaviour is observed when you add FirstOrDefault() to your query.
    If you are using ToList() OpenAccess issues similar query to the one created by Entity Framework.

    Changing the shape of the query enables us to load the Company instance by Id and push it to the cache for reuse later.
    If you have any concerns about the change of the shape, please share them with us so we can discuss them and see if there are any possible work around.

    Kind regards,
    Viktor Zhivkov
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  3. DevCraft banner
Back to Top