Foreach statement cannot operate on variables of type '?' because '?' does not contain a public definition for 'GetEnumerator'

Question

I did some updates to a new copy of a database that I am using and generated the edmx file from the database, I fixed a couple stored procs that were giving me issues, however in this one method I keep getting this error

Error CS1579 foreach statement cannot operate on variables of type '?' because '?' does not contain a public definition for 'GetEnumerator'

and I think its tied in with this error

Error CS1936 Could not find an implementation of the query pattern for source type 'int'. 'Select' not found

I have looked around for a solution to this and haven't really found anything solid.

The method in my DAL that is throwing the error is

public List<NewCustomer> GetCustomerToEditByID(int id)
    {
        ExoEntities = new ExoEntities();
        List<NewCustomer> lst = new List<NewCustomer>();

        var query = from a in  ExoEntities.usp_GetCustomerByID(id)
                    select a;

        foreach (var b in query)
        {
            lst.Add(new NewCustomer
            {
                CustomerID = b.CustomerID,
                FirstName = b.FirstName,
                LastName = b.LastName,
                YearBuilt = b.YearBuilt,
                Line1 = b.Line1,
                Line2 = b.Line2,
                City = b.City,
                ZipCode = b.ZipCode,
                StateID = (int)b.StateID,
                StateName = b.StateName,
                County = b.County,
                SubDivisionID = (int)b.SubDivisionID,
                ContactName = b.ContactName,
                Phone = b.Phone,
                OtherPhone = b.OtherPhone,
                Cell = b.Cell,
                Fax = b.Fax,
                Email = b.Email
            });
        }

        return lst;
    }

the data class is

public class NewCustomer
    {
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string DateCreated { get; set; }
        public int CreatedBy { get; set; }
        public string YearBuilt { get; set; }
        public byte IsActive { get; set; }
        public int CustomerTypeID { get; set; }

        // Address
        public string Line1 { get; set; }
        public string Line2 { get; set; }
        public string Line3 { get; set; }
        public string City { get; set; }
        public string ZipCode { get; set; }
        public int StateID { get; set; }
        public string StateName { get; set; }
        public string County { get; set; }
        public int SubDivisionID { get; set; }

        // Contact
        public string ContactName { get; set; }
        public string Phone { get; set; }
        public string OtherPhone { get; set; }
        public string Cell { get; set; }
        public string Pager { get; set; }
        public string Fax { get; set; }
        public string Email { get; set; }
        public byte ContactIsActive { get; set; }
    }

This is the EF context class for the stored procedure

public virtual int usp_GetCustomerByID(Nullable<int> customerID)
    {
        var customerIDParameter = customerID.HasValue ?
            new ObjectParameter("CustomerID", customerID) :
            new ObjectParameter("CustomerID", typeof(int));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("usp_GetCustomerByID", customerIDParameter);
    }

and my stored procedure is

    Create procedure [dbo].[usp_GetCustomerByID]
(
    @CustomerID int
)
AS
SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @ERROR_SEVERITY int,
        @MESSAGE varchar(1000),
        @ERROR_NUMBER int,
        @ERROR_PROCEDURE nvarchar(200),
        @ERROR_LINE int,
        @ERROR_MESSAGE nvarchar(4000);

begin try
    select 
          caxref.CustomerID,
          caxref.AddressID,
          customer.FirstName,
          customer.LastName,
          customer.YearBuilt,
          address.Line1,
          address.Line2,
          address.City,
          address.ZipCode,
          address.StateID,
          address.County,
          state.StateName,
          address.SubDivisionID,
          contact.ContactName,
          contact.Phone,
          contact.OtherPhone,
          contact.Cell,
          contact.Fax,
          contact.Email
    from [CustomerAddressXREF] caxref
        left join [Customer] customer on customer.CustomerID = caxref.CustomerID
        left join [Address] address on address.AddressID = caxref.AddressID
        left join [SubDivision] subdivision on subdivision.SubDivisionID = address.SubDivisionID
        left join [CustomerContactXREF] ccxref on ccxref.CustomerID = customer.CustomerID
        left join [Contact] contact on contact.ContactID = ccxref.ContactID
        inner join [State] state on state.StateID = address.StateID
    where customer.CustomerID = @CustomerID

end try
BEGIN CATCH
    SET @ERROR_SEVERITY = ISNULL(ERROR_SEVERITY(),'');
    SET @ERROR_NUMBER = ISNULL(ERROR_NUMBER(),'');
    SET @ERROR_PROCEDURE = ISNULL(ERROR_PROCEDURE(),''); 
    SET @ERROR_LINE = ISNULL(ERROR_LINE(),'');
    SET @ERROR_MESSAGE = ISNULL(ERROR_MESSAGE(),'');

    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
        BEGIN
            --PRINT N'The transaction is in an uncommittable state. Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;

    -- Test if the transaction is active and valid.
    IF (XACT_STATE()) = 1
        BEGIN
            --PRINT N'The transaction is committable. Committing transaction.'
            COMMIT TRANSACTION;   
        END;

    SET @MESSAGE = 'Error Occured in Stored Procedure ' + cast(@ERROR_PROCEDURE as varchar(200)) + 
                    '; Line Number ' + cast(@ERROR_LINE as varchar) + 
                    '; Message: [' + cast(@ERROR_NUMBER as varchar) + '] - '
                    + cast(@ERROR_MESSAGE as varchar(255))

    RAISERROR(@MESSAGE, @ERROR_SEVERITY, 1);
END CATCH;

I have tested the stored procedure in SSMS and it returns the all the data that I need

So I am not sure where the problem is lying.

I have deleted my EDMX file a couple times and regenerated it from the database, but that isn't working, I still get those 2 errors. Its probably blantantly obvious to a solution, but its not popping out at me

Answer 1

Your usp_GetCustomerByID C# function returns a single int value, not a collection of objects, so you can't use it as the source of a query. from a in ExoEntities.usp_GetCustomerByID(id) is not valid.

The return type of usp_GetCustomerByID should be an IEnumerable<sometype> if you want to query it. You might me able to use IEnumerable<NewCustomer> as the return type but it's not clear if that is your entity type. Then you wouldn't have to iterate it and create new NewCustomer objects. You could just call return usp_GetCustomerByID(id).ToList()

Also, the query from a in <source> select a is essentially returning an iterator over <source>, so you could just use:

foreach (var b in <source>)