LINQ doesn't like TINYINT PK IDENTITY fields

So, to save space (and it is a bit archaic, but why not) I sometimes use tinyint and smallint for the PK IDENTITY columns in small lookup tables.

For example, a list of counties or congressional districts for a state government project.  Will never be more than 255 or negative.  Tinyint.  1 byte instead of 4.

Yeah yeah, I'm aware that in the long run, that adds up to very little saved disk space.  Very little.  Still, I can't help but be tight with data type constraints.  Its not like I will be allowing every column to be declared varchar(8000), much less varchar(max), even when "disk space is cheap".  Its not like I'll be using bigint instead of int when I know I'll never get more than 2 billion records. 

Except that when your developers want to use LINQ, you can't use a TINYINT IDENTITY column. Stupid error.

Changed it, begrudgingly, to smallint.  2 bytes instead of 4.  Grrr...

Read more:
http://linqinaction.net/blogs/roller/archive/2007/11/28/linq-to-sql-doesn-t-support-identity-tinyint.aspx

Roll call - when all members are present

Had to write a neat query that was a bit of fun-

A protocol is a set of services. A clinic offers services, independently of protocols. Should a clinic offer all those services, it satisfies a protocol. Return a list of all satisfied protocols given a single clinic.

(Pardon the spacing and syntax, but blogspot nukes my tabs and spaces. I must figure out a better way to post code.)

SELECT
p.ID
FROM
dbo.Protocol p
WHERE
p.IsActive = 1
AND p.ID NOT IN (
SELECT
ps.ProtocolID
FROM
dbo.ProtocolServices ps
LEFT JOIN
dbo.ClinicServices cs
ON
ps.ServiceID = cs.ServiceID
and cs.IsActive = 1
and cs.clinicid = 987
WHERE
ps.IsActive = 1
and cs.ID is null
)

What the query does is to say - give me all Protocols, except the ones where the service list is not satisfied. The subquery looks at the join tables (they are named just as they behave, per naming conventions) and determines any Protocols where the Clinic is not satisfying all Services - that is, where there is a null in the LEFT JOIN on ClinicServices.

Now, here's the fun part.

The developer I was working with, despite all his talents, is a groupie for lambda expressions, which I understand are some sort of dark magic concocted in obsidian towers for purposes dire and contradictory to the good will of databases.

Despite all that hyperbole, here's what he came up with to convert the above query to the below nonsense, err, λ expression. From what he explained to me, Lambda expressions are dynamic and use the declaratives seen below while LINQ is more hard coded. I welcome further input with the caveat that LINQ, EF and other systems for generating bad TSQL are systema non grata around here. :)

var protocols = Protocols.Where(p => p.IsActive);
var activeProtocolServices = ProtocolServices.Where(ps => ps.IsActive);
var clinicServices = ClinicServices
.Where(cs => cs.IsActive)
.Where(cs => cs.ClinicID == 987);
var allProtocolServicesForClinics = activeProtocolServices.Join(clinicServices, ps => ps.ServiceID, cs => cs.ServiceID, (ps, cs) => ps);
var protocolServicesNotApplicableForClinic = activeProtocolServices.Except(allProtocolServicesForClinics);
var protocolsForClinic = protocols.Except(protocolServicesNotApplicableForClinic.Select(ps => ps.Protocol));