none
Need help converting a stored procedure with table variables to Linq RRS feed

  • Question

  • Hi, I'm trying convert a stored procedure to linq. The objective is to find credit union branches within a zip code. I have a stored procedure getZipCodesWithinRadius that works well. Returns zip codes and the distance from the target zip code. I have a table of branches with address data, creditunionBranches. I use table variables to create result sets and then join them to get the final list. I'm having trouble converting this to linq. 

    Stored Procedure Code:

    DECLARE @Zip_Temp TABLE (
    		TargetZipCode NVARCHAR(50),
    		Distance FLOAT )
    
    DECLARE @CreditUnionsAndBranches TABLE  (
    		cuId int,
    		cuName NVARCHAR(255),
    		branchName NVARCHAR(255),
    		briefDescription NVARCHAR(600),
    		zip NVARCHAR(50),
    		distance float,
    		isFullSearch bit)
    		
    INSERT INTO @Zip_Temp (TargetZipCode, Distance)
    EXEC dbo.GetZipCodesWithinRadius @Zip, @Radius
    
    INSERT INTO @CreditUnionsAndBranches (cuId, cuName, branchName, briefDescription, zip, distance)
    select c.creditunionId, c.name, b.branchName, c.briefDescription, b.zip, z.distance 
    	from creditunion as c
    	join creditunionBranch as b on b.creditunionId = c.creditunionId
    	join @Zip_Temp as z on SUBSTRING (b.zip,1,5) = z.targetzipcode
    	where c.inactive = 0
    
    	select 
    		c.cuId,
    		cuName, 
    		briefDescription,
    		MIN (c.distance) as Distance
    	FROM @CreditUnionsAndBranches as c
    	join @Zip_Temp as z on SUBSTRING (c.zip,1,5) = z.targetzipcode
    	group by c.cuId, c.cuName, c.briefDescription, c.isFullSearch
    	order by distance, c.cuName
    Here's what I've tried so far:

            CULookupDataContext CUData = new CULookupDataContext();
    
            var zipCodes = CUData.GetZipCodesWithinRadius(zipTextBox.Text, 5);
    
            var query = from b in CUData.CreditUnionBranches
                         from z in zipCodes
                         where z.TargetZipCode.Contains(b.zip)
                               select new
                              {
                                  cuName = b.branchName,
                                  briefDescription = "description",
                                  distance = z.Distance
                              };
            e.Result = query;
    I'm get the  Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.  error message when I try to run this. 

    Thanks. All help is greatly appreciated.
    Tuesday, March 16, 2010 2:54 AM

Answers

  • Hello Tim,

    The error you encountered is caused by local sequence is used in a JOIN query with the database table.  It is not supported since we cannot make the all the client sequence value into the query to make a JOIN query with the database table.  Only one method is supported as stated, the Contains() method, which is translated into the WHERE IN SQL statement.   You may want to use such a query instead:

    var targetZipCodes = zipCodes.Select(z => z.TargetZipCode.SubString(0, 5));

    var query =  (from b in CUData.CreditUnionBranches
                        where targetZipCodes.Contains(b.zip)
                        select new
                        {
                             cuName = b.branchName,
                             b.zip
                        }).AsEnumerable().Join(zipCodes, b => b.zip, z => z.TargetZipCode.SubString(0, 5), (b, z) =>
                           new
                           {
                                b.cuName,
                                briefDescription = "description",
                                distance = z.Distance
                           });

    We first retrieve the data use the WHERE IN query and make a LINQ to Objects JOIN query later to get the data we want.

    Have a nice day!

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 22, 2010 1:50 PM
    Moderator

All replies

  • Hello Tim,

    The error you encountered is caused by local sequence is used in a JOIN query with the database table.  It is not supported since we cannot make the all the client sequence value into the query to make a JOIN query with the database table.  Only one method is supported as stated, the Contains() method, which is translated into the WHERE IN SQL statement.   You may want to use such a query instead:

    var targetZipCodes = zipCodes.Select(z => z.TargetZipCode.SubString(0, 5));

    var query =  (from b in CUData.CreditUnionBranches
                        where targetZipCodes.Contains(b.zip)
                        select new
                        {
                             cuName = b.branchName,
                             b.zip
                        }).AsEnumerable().Join(zipCodes, b => b.zip, z => z.TargetZipCode.SubString(0, 5), (b, z) =>
                           new
                           {
                                b.cuName,
                                briefDescription = "description",
                                distance = z.Distance
                           });

    We first retrieve the data use the WHERE IN query and make a LINQ to Objects JOIN query later to get the data we want.

    Have a nice day!

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 22, 2010 1:50 PM
    Moderator
  • The error you encountered is caused by local sequence is used in a JOIN query with the database table.  It is not supported since we cannot make the all the client sequence value into the query to make a JOIN query with mp4 database table.  Only one method is supported as stated, the Contains() method, which is translated into the WHERE IN SQL statement.  

    Do you mean all the JOIN query in LINQ are not supported?
    Saturday, June 19, 2010 10:36 AM