Linq-To-SQL Marc Gravell's InRange Extension

Question

I'm tring to use the InRange extension Marc Gravell wrote here:

LINQ Expression to return Property value?

This is my code so far:

// Fetch list of visit Ids and distinct Ips that fall into the date range
var q = (from c in db.tblTrackerVisits where c.Date >= MinDate select new { c.ID, c.IPID });
List<int> VisitIDs = q.Select(c => c.ID).ToList();
List<int> DistinctIPs = q.Select(c => c.IPID).Distinct().ToList();

// List of all campaigns that have visitors
var Campaigns = db.tblTrackerVariables
    .Where(c => 
        c.TypeID == Settings.CampaignTrackerVariableTypeID
        //&& db.tblTrackerVisitVariables.Any(d=>VisitIDs.Contains(d.VisitID) && d.VariableID == c.ID)
        && db.tblTrackerVisitVariables.InRange(x => x.VisitID, 1500, VisitIDs)
    )
    .Select(c => new { c.ID, c.Name }).OrderBy(c=>c.Name);

However this throws:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<tblTrackerVisitVariable>' to 'bool'

I'm not sure if I'm using it correctly, can anyone give me some pointers? The commented out && above it:

//&& db.tblTrackerVisitVariables.Any(d=>VisitIDs.Contains(d.VisitID) && d.VariableID == c.ID)

Is the old working code (but it throws the too many params error so I have to resort to this extension method).

wait.... what am I being blamed for now? ;p – Marc Gravell Jan 23 '12 at 13:29 — Marc Gravell, Jan 23 '12 at 13:29

Tudor · Answer 1 · 2012-01-23T13:01:45.113

3

Mark's extension method returns an IEnumerable, not a bool, and therefore cannot be included in a logical expression. I you want to check if the result is non-empty try the Any method:

db.tblTrackerVisitVariables.InRange(x => x.VisitID, 1500, VisitIDs).Any()

edited Jan 23 '12 at 13:01

answered Jan 23 '12 at 12:47

Tudor

61,523
12
102
142

Ah crap, so if I'm getting the `The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.` what are my options? Do I have to redesign all my queries? – Tom Gullen Jan 23 '12 at 12:48
Do you want to check if the result of InRange has any elements? – Tudor Jan 23 '12 at 12:51
3

Please use `.Any()` instead of `.Count() > 0` – Oliver Jan 23 '12 at 12:53
@Oliver: Added your suggestion. – Tudor Jan 23 '12 at 12:56
@TomGullen Yes, you will. This goes down to the .Net Sql client level and Sql server (I believe). Typically when I hit this, I break the parameters into chunks and repeat the query for each set of 2000 parameters, then union the result together on the client. – Andy Jan 23 '12 at 13:18
@Oliver, I use any but if after the query I do `List VariableIDs = Campaigns.Select(c => c.ID).ToList();` it throws the error at runtime `Method 'System.Collections.Generic.IEnumerable`1[tblTrackerVisitVariable] InRange[tblTrackerVisitVariable,Int32](System.Linq.IQueryable`1[tblTrackerVisitVariable], System.Linq.Expressions.Expression`1[System.Func`2[tblTrackerVisitVariable,System.Int32]], Int32, System.Collections.Generic.IEnumerable`1[System.Int32])' has no supported translation to SQL.` – Tom Gullen Jan 23 '12 at 13:36

Linq-To-SQL Marc Gravell's InRange Extension

1 Answers1