Entity Framework Woes

I CAN HAS DYNAMIC SQLZ PLZ??

Dynamic SQL is a serious issue for Entity Framework.  Don't believe me?

Look here, here, and here for some non-answers and more frustration.  I did dig further and we tried some other things, but nothing was 'easy' and just fell into place.

I even tried the SET FMTONLY OFF, to no avail.

Whats the real issue here?  Entity framework needs to be able to scan the stored proc to get the values it returns to create a complex object.  That's all we are trying to do.  If you put the stuff in a stored procedure inside of a dynamic sql, Entity Framework can't see it, and then the Software Engineers (some of them anyway) cry.  A real solution is to build out the complex type by hand.  We all know that many computer programmers are pretty freaking lazy (myself included) so this 'by hand' term is not in our dictionary. (Another solution would be to program a thing that builds out the complex type, but i digress.)  Sometimes people let their laziness get the better of them and we get these abominations:

SELECT TOP 100
 m.Id,
 c.CompanyName AS ClientName,
 m.FirstName,
 m.LastName,
 m.MiddleName,
 m.Address1,
 m.Address2,
 m.City,
 m.Suffix,
 m.StateProvince,
 m.PostalCode,
 m.HomePhone,
 m.Email,
 m.Active,
 m.MemberShipNo
FROM Members m
WHERE (@MembershipNumber IS NULL OR (m.MemberShipNo LIKE @MembershipNumber + '%'))
 AND (@FirstName IS NULL OR (FirstName LIKE @FirstName + '%'))
 AND (@LastName IS NULL OR (LastName LIKE @LastName + '%'))
 AND (@ZipCode IS NULL OR (m.PostalCode LIKE @ZipCode + '%'))
 AND (@PhoneNumber IS NULL OR (m.HomePhone LIKE @PhoneNumber + '%'))
 AND (@EmailAddress IS NULL OR (m.Email LIKE @EmailAddress + '%'))

Don't do this because of this.  Every time you do, you murder a kitten and risk being chased by a DBA with a hatchet.

So the following is what we did -- we broke one of the statements out of the dynamic SQL and stuck it at the top of the stored procedure like so:

 SELECT DISTINCT TOP 2500  
 m.[Id],
 m.[ClientId],
 m.[MemberShipNo],
 m.[Active],
 m.[SignedAuth],
 m.[EnrollmentDate],
 m.[SSN_NIN],
 m.[DOB],
 m.[FirstName],
 m.[MiddleName],
 m.[LastName],
 m.[Address1],
 m.[City],
 m.[StateProvince],
 m.[PostalCode],                                                                                     
 m.[Country],
 m.[Void],
 m.[PackageId],
 p.[ProductLabel] AS PackageLabel,
 m.[VendorSelection],
 m.[OnlineCredit],
 m.[MinorProductID],
 r.[Id] AS CaseId
 FROM [dbo].[Members] AS m
 LEFT OUTER JOIN [dbo].[Packages] AS p ON m.[PackageId] = p.[Id]
 OUTER APPLY (SELECT TOP 1 r.[Id] 
     FROM [dbo].[Rq_FraudCase] AS r
     WHERE r.[MembersId] = m.[Id] 
     ORDER BY r.[Id] DESC) AS r
 WHERE m.[Void] = 0 AND 1=2
  END
-- Above sproc is what the Dynamic SQL After this returns

This almost got it, but now I am returning two sets of records to Entity Framework and it poops its pants. I thought why not put the 1=2 at the beginning?

IF 1=2
  BEGIN
 SELECT DISTINCT TOP 2500  
 m.[Id],
 m.[ClientId],
 m.[MemberShipNo],
 m.[Active],
 m.[SignedAuth],
 m.[EnrollmentDate],
 m.[SSN_NIN],
 m.[DOB],
 m.[FirstName],
 m.[MiddleName],
 m.[LastName],
 m.[Address1],
 m.[City],
 m.[StateProvince],
 m.[PostalCode],                                                                                     
 m.[Country],
 m.[Void],
 m.[PackageId],
 p.[ProductLabel] AS PackageLabel,
 m.[VendorSelection],
 m.[OnlineCredit],
 m.[MinorProductID],
 r.[Id] AS CaseId
 FROM [dbo].[Members] AS m
 LEFT OUTER JOIN [dbo].[Packages] AS p ON m.[PackageId] = p.[Id]
 OUTER APPLY (SELECT TOP 1 r.[Id] 
     FROM [dbo].[Rq_FraudCase] AS r
     WHERE r.[MembersId] = m.[Id] 
     ORDER BY r.[Id] DESC) AS r
 WHERE m.[Void] = 0
  END
-- Dynamic SQL below this sproc

And Entity Framework is now happy, and the Dynamic SQL runs without issue.  If someone comes along and tells me this borks the explain plan or something else horrible happens, I will investigate and change my tune.  For now, this is a good way to get a tool to allow us to run a stored proc with Dynamic SQL without needing to do tedious amounts of work to get there.