Linq to SQL method defaults to ISingleResult

Question

I have a stored procedure that returns all the columns from a table (InstitutionEntityIP) that maps to the DBML object of the same name. It will return n number of records. However, the codebehind defaults to ISingleResult. I would like to have it return an IEnumerable.

I'm getting an error message 'The query results cannot be enumerated more than once.'

My code looks like this:

//ContentRepository.cs
private readonly IGIGlobalDataContext db;
// ....
public IEnumerable<InstitutionEntityIP> GetInstitutionEntityIPs(long fromIp, long toIp)
{
    return 
        from ip in db.InstitutionEntityIPs
        where
        (
            (fromIp >= ip.FromIPInteger && fromIp <= ip.ToIPInteger) || //From IP Address parameter is the same or encompassed
            (toIp >= ip.FromIPInteger && toIp <= ip.ToIPInteger) || //To IP Address parameter is the same or encompassed
            (fromIp <= ip.FromIPInteger && toIp >= ip.FromIPInteger) || //Any From IPs in the DB between the parameters 
            (fromIp <= ip.ToIPInteger && toIp >= ip.ToIPInteger) //Any To IPs in the DB between the parameters 
        ) &&
        ip.IsActive &&
        ip.InstitutionEntity.IsActive &&
        Convert.ToDateTime((ip.InstitutionEntity.ExpirationDate ?? (DateTime?)DateTime.Now)) >= DateTime.Now &&
        ip.InstitutionEntity.Institution.IsActive &&
        Convert.ToDateTime((ip.InstitutionEntity.Institution.ExpirationDate ?? (DateTime?)DateTime.Now)) >= DateTime.Now
        select ip;
}
public IEnumerable<InstitutionEntityIP> GetInstitutionEntityIPs(string fromIp, string toIp)
{
    // SearchForConflictingIPs is the method name created when I drop my 
    // stored procedure (also called SearchForConflictingIPs) on the DBML layout
    var ips = db.SearchForConflictingIPs(fromIp, toIp); // returns ISingleResult<InstitutionEntityIP>
    // VVVVVVVVV
    return ips.ToList(); // <-- Needed to specify the .ToList() here <--
    // ^^^^^^^^^
}

// I actually call the method from another class
public static IEnumerable<IGIGlobal_DAL.InstitutionEntityIP> ConflictingIPs(string start, string end)
{
    var cr = new ContentRepository();
    return cr.GetInstitutionEntityIPs(start, end);
}

public RangeValidationResult Validate(bool isUpdate = false, int institutionEntityIPID = 0)
{
    var conflicts = ConflictingIPs(Start.ToString(), End.ToString());

    if (conflicts.Any()) // <---- 'The query results cannot be enumerated more than once.'
    {
        var conflictingEntities = string.Join(", ", conflicts.Select(c => c.InstitutionEntity.InstitutionEntity1).Distinct());
    }
}

If you need any more information from me please comment.

Where does `SearchForConflictingIPs` come from? What do you mean by the bit you've labelled "actual call"? It's very unclear what's going on at the moment... — Jon Skeet, May 29 '15 at 16:02
Updated with some comments and some missing pieces. Sorry I hadn't included them. — M Kenyon II, May 29 '15 at 16:59
Found this post: http://stackoverflow.com/questions/5723555/the-result-of-a-query-cannot-be-enumerated-more-than-once — M Kenyon II, May 29 '15 at 17:19
You still haven't shown the declaration of `SearchForConflictingIPs` or explained what created it... — Jon Skeet, May 29 '15 at 17:24
@JonSkeet he specified this in comments inside method GetInstitutionEntityIPs that SearchForConflictingIPs is generated from storedProcedure in DBML file. — vendettamit, May 29 '15 at 19:30
@vendettamit: Ah, I hadn't seen that part of the edit. It would have been much clearer if that had just been text in the question rather than in comments... — Jon Skeet, May 29 '15 at 20:13

score 1 · Answer 1 · edited May 23 '17 at 11:54

1

I needed to specify the .ToList() in the public IEnumerable GetInstitutionEntityIPs(string fromIp, string toIp) method.

Found the info I needed here:
The result of a query cannot be enumerated more than once

edited May 23 '17 at 11:54

Community

1
1

answered May 29 '15 at 17:20

M Kenyon II

4,136
4
46
94

score 0 · Answer 2 · edited Oct 07 '16 at 05:31

The function generated by stored procedures in DBML file would return ISingleResult and it behaves like a DataReader. You are only allowed to get the enumerator to loop through only one time.

In your case:

 if (conflicts.Any()) // <---- 'The query results cannot be enumerated more than once.'

Means the enumerator has already been used. Here Any() also tries to get the enumerator to perform check if there's at least 1 record exists.

So you need to convert the every ISingleResult to a list using that enumerator, if you have requirement to loop through multiple times over the collection. So you have to live with ToList() in that case.

Linq to SQL method defaults to ISingleResult

2 Answers2