Here's my query (EF Core 7 and resulting raw SQL):
Me.Context.Patients.Where(Function(Patient) Patient.BloodType.ReceivesFrom.HasFlag(BloodType))
SELECT "p"."Id", "p"."BloodTypeId", "p"."CityId", "p"."Code", "p"."Email", "p"."FirstName", "p"."LastName", "p"."ZipCodeId", "b"."Type"
FROM "Patients" AS "p"
INNER JOIN "BloodTypes" AS "b" ON "p"."BloodTypeId" = "b"."Id"
WHERE ("b"."ReceivesFrom" & 4) = 4
...and here's the result:
-------------------------------------------------------------------------------------------------------------
| Id | BloodTypeId | CityId | Code | Email | FirstName | LastName | ZipCodeId | Type |
-------------------------------------------------------------------------------------------------------------
| 1 | 4 | 46 | U6UJI | [email protected] | Abbigail | Cremin | 12 | 4 |
| 2 | 5 | 88 | LV892 | [email protected] | Donnell | McKenzie | 94 | 8 |
| 5 | 8 | 44 | 5F6TN | [email protected] | Nedra | Stokes | 66 | 64 |
| 7 | 5 | 16 | 6LQ61 | [email protected] | Jairo | Jakubowski | 69 | 8 |
| 10 | 5 | 8 | BI56C | [email protected] | Modesto | Reichert | 33 | 8 |
| 11 | 5 | 94 | R8LP2 | [email protected] | Raphaelle | Murphy | 47 | 8 |
| 16 | 5 | 66 | 8TC6V | [email protected] | Daphney | Bechtelar | 94 | 8 |
| 17 | 4 | 45 | DM52B | [email protected] | Durward | Larson | 67 | 4 |
-------------------------------------------------------------------------------------------------------------
The problem is that the query results are very inaccurate.
Here's the BloodTypes data:
----------------------------------------
| Id | ReceivesFrom | DonatesTo | Type |
----------------------------------------
| 1 | 0 | 0 | 0 |
| 2 | 3 | 255 | 1 |
| 3 | 3 | 255 | 2 |
| 4 | 15 | 204 | 4 |
| 5 | 15 | 204 | 8 |
| 6 | 51 | 240 | 16 |
| 7 | 51 | 240 | 32 |
| 8 | 255 | 192 | 64 |
| 9 | 255 | 192 | 128 |
----------------------------------------
The supporting enum:
<Flags>
Public Enum BloodTypes As Byte
<Description("?")> Unknown = 0
<Description("O+")> OPositive = 1
<Description("O-")> ONegative = 2
<Description("A+")> APositive = 4
<Description("A-")> ANegative = 8
<Description("B+")> BPositive = 16
<Description("B-")> BNegative = 32
<Description("AB+")> ABPositive = 64
<Description("AB-")> ABNegative = 128
End Enum
...and the Patients data (fake, created with Bogus):
-----------------------------------------------------------------------------------------------------------
| Id | BloodTypeId| CityId | Code | Email | FirstName | LastName | ZipCodeId |
-----------------------------------------------------------------------------------------------------------
| 1 | 4 | 46 | U6UJI | [email protected] | Abbigail | Cremin | 12 |
| 2 | 5 | 88 | LV892 | [email protected] | Donnell | McKenzie | 94 |
| 3 | 6 | 85 | SNTB4 | [email protected] | Paige | Watsica | 4 |
| 4 | 2 | 24 | 4XMHO | [email protected] | Gregg | Wilkinson | 26 |
| 5 | 8 | 44 | 5F6TN | [email protected] | Nedra | Stokes | 66 |
| 6 | 2 | 88 | 6C1LG | [email protected] | Brice | Runolfsson | 55 |
| 7 | 5 | 16 | 6LQ61 | [email protected] | Jairo | Jakubowski | 69 |
| 8 | 6 | 12 | 50U3K | [email protected] | Fredy | Heathcote | 54 |
| 9 | 7 | 2 | DD4YB | [email protected] | Dorothea | Littel | 20 |
| 10 | 5 | 8 | BI56C | [email protected] | Modesto | Reichert | 33 |
| 11 | 5 | 94 | R8LP2 | [email protected] | Raphaelle | Murphy | 47 |
| 12 | 7 | 92 | AALG8 | [email protected] | Willy | Pouros | 28 |
| 13 | 7 | 82 | LCH1T | [email protected] | Everardo | Lang | 6 |
| 14 | 6 | 8 | 8HNN2 | [email protected] | Koby | Purdy | 53 |
| 15 | 3 | 24 | FWG31 | [email protected] | Gardner | Mueller | 27 |
| 16 | 5 | 66 | 8TC6V | [email protected] | Daphney | Bechtelar | 94 |
| 17 | 4 | 45 | DM52B | [email protected] | Durward | Larson | 67 |
| 18 | 7 | 57 | 4750K | [email protected] | Joelle | Bashirian | 58 |
| 19 | 2 | 18 | O8Z0Q | [email protected] | Loy | Ratke | 98 |
| 20 | 1 | 6 | 8UKAB | [email protected] | Christa | Gibson | 48 |
-----------------------------------------------------------------------------------------------------------
See the problem?
A+ blood (flag 4 in the BloodTypes enum) can receive from types A+/- and O+/- (source: Red Cross), so the query should be returning exactly 11 rows whose BloodTypeId is either 2, 3, 4 or 5. (I've ensured that 11 random rows satisfying this requirement exist in my Patients test data.)
Flag 4 (A+) has a ReceivesFrom value of 15, which is the sum of types 1, 2, 4 and 8 (O+, O-, A+ and A-).
Here's my BloodTypes table definition (no indexes, but I doubt that matters):
CREATE TABLE "BloodTypes" (
"Id" INTEGER NOT NULL,
"ReceivesFrom" INTEGER NOT NULL DEFAULT 0,
"DonatesTo" INTEGER NOT NULL DEFAULT 0,
"Type" INTEGER NOT NULL DEFAULT 0,
CONSTRAINT "PK_BloodTypes" PRIMARY KEY("Id" AUTOINCREMENT)
);
The frustrating part is that the query results include invalid rows and omit valid rows. For example, check ID#5 in the results: there's no way that 64 (AB+) fits into 15. That's a mathematical impossibility.
And where are PatientIds 4, 6, 15 and 19? Why were they omitted? They're within the scope of acceptable blood types for A+.
What's going on here? Why is SQLite producing such wildly inaccurate results?
It turns out that the query is behaving correctly. It's my expectations that were incorrect. I was looking at the problem 180° backward.
Instead of trying to shoehorn AB+ (64) into the A+.ReceivesFrom aggregate (15)—which obviously doesn't work—we must go the other way and fit A+ (4) into the AB+.ReceivesFrom aggregate (255). That works.
Which makes perfect sense when we break it down.
The rule: We want all patients who can receive from A+. Simple.
That's A and AB, according to the chart. Those two have ReceivesFrom aggregates of 15 and 255, respectively. A+ (4) fits into both of those, thus the query returns all A/AB blood types.
Problem solved. In fact, it never even was a problem.