Comparing IPAddress (stored as varbinary)

Question

I have an IPAddress column on my Activity table. This is stored as a varbinary(16) so that it can be efficient (moreso than storing as a string) and also support IPv6. When I store, I basically get the value of (new System.Net.IPAddress("127.0.0.1")).GetAddressBytes().

What I want to be able to do is search for all IP addresses that begin with certain bytes, e.g. "127.*". I can easily get the bytes for that, so just assume that I am able to get new byte[] { 127 }.

Given that, how can I actually write a LINQ to SQL query to get me the data I want?

Sadly, I don't have StartsWith, though I essentially want the equivalent of Activity.Where(a => a.IPAddress.StartsWith(new byte[] { 127 })).

Answer 1

If the data is returned as a byte array, why not reference the first byte of the array? Sounds like;

Activity.Where(a => a.IpAddress[0] == 127);

might be what your looking for?

You could store the IP address as a hex string, where 127.0.0.1 = "7F000001" then if you want to find an IP address starting with 192.168.* you can use

Activity.Where(a => a.IpAddress.StartsWith("C0A8"));

Answer 2

A while ago, I had to find the location of a given IP. We got the IP from the request. There are free databases which gave us this mapping. In IPv4, when we say the IP as "a.b.c.d" it is essentially a * (256^3) + b * (256^2) + c * (256) + d.

http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp

so when you say you want an IP address starting with "a", you are looking for IPs between a * 256^ 3 and a * 256^3 + 256 * (256^2) (b = 256) + 256 *(256) (c=256) + 256( d=256) (lower / upper limit may vary a little bit depending on whether you want to include/exclude the limits).

That said, there are specific IPs reserved for specific purposes(like 127.0.0.1 which is localhost, 0.0.0.0 cannot be an IP etc).

So your linq query would be

"from i in iList where i >= MIN && i <= MAX select i;"

where iList is your initial list MIN is your min value for your range MAX is your max value for your range