none
Why FirstOrDefault create an Outer Apply RRS feed

  • Question

  • Hi, I would like to know how to replace the Generated Outer Apply by an Outer Apply.

    Here is the LinqToSql query

      var ListLocation = from s in _db.Locations.Where(p =>
                                                              p.IsDelete == false
                                                             )
                                  
                                   select new LocationViewModel()
                                   {
                                       LocationID = s.LocationID,
                                       LocationTypeID = s.LocationTypeID,
                                       Long = s.Address.Longitude,
                                       Lat = s.Address.Latitude,
                                       FileNumber = s.LocationFile,
                                       State = s.Address.City.Province.PrvName,
                                       City = s.Address.City.CityName,
                                       Address = s.Address.CivicNumber + " " + s.Address.Street,
                                       Status =s.LocationType.LocationTypeTexts.FirstOrDefault(p => p.LangID == lang).Txt
                              
                                   };

     

    and this is the generated Sql query

    SELECT [Filter1].[LocationID]     AS [LocationID],
           [Filter1].[LocationTypeID] AS [LocationTypeID],
           [Filter1].[Longitude]      AS [Longitude],
           [Filter1].[Latitude]       AS [Latitude],
           [Filter1].[LocationFile]   AS [LocationFile],
           [Filter1].[PrvName]        AS [PrvName],
           [Filter1].[CityName1]      AS [CityName],
           [Filter1].[CivicNumber]
             + N' '
             + [Filter1].[Street] AS [C1],
           [Element1].[Txt]           AS [Txt]
    FROM   (SELECT [Extent1].[LocationID]     AS [LocationID],
                   [Extent1].[LocationFile]   AS [LocationFile],
                   [Extent1].[LocationTypeID] AS [LocationTypeID],
                   [Extent2].[CivicNumber]    AS [CivicNumber],
                   [Extent2].[Street]         AS [Street],
                   [Extent2].[Longitude]      AS [Longitude],
                   [Extent2].[Latitude]       AS [Latitude],
                   [Extent4].[PrvName]        AS [PrvName],
                   [Extent5].[CityName]       AS [CityName1]
            FROM   [dbo].[Locations] AS [Extent1]
                   LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2]
                     ON [Extent1].[AddressID] = [Extent2].[AddressID]
                   LEFT OUTER JOIN [dbo].[Cities] AS [Extent3]
                     ON [Extent2].[CityID] = [Extent3].[CityID]
                   LEFT OUTER JOIN [dbo].[Provinces] AS [Extent4]
                     ON [Extent3].[PrvID] = [Extent4].[PrvID]
                   LEFT OUTER JOIN [dbo].[Cities] AS [Extent5]
                     ON [Extent2].[CityID] = [Extent5].[CityID]
            WHERE  0 = [Extent1].[IsDelete]) AS [Filter1]
           OUTER APPLY (SELECT TOP ( 1 ) [Extent6].[ID]     AS [ID],
                                         [Extent6].[LangID] AS [LangID],
                                         [Extent6].[Txt]    AS [Txt]
                        FROM   [dbo].[LocationTypeTexts] AS [Extent6]
                        WHERE  ([Filter1].[LocationTypeID] = [Extent6].[ID])
                               AND ([Extent6].[LangID] = 1 /* @p__linq__0 */)) AS [Element1]

     

    I would like to replace the outer apply part with a simple JOIN like this.

     

    How can I do this ?

    Thanks a lot.

     

     

    Tuesday, June 14, 2011 2:12 PM

Answers

All replies