Split flags into normalized table
I have a table that looks like
OldThing
id | Value | Flags | ...
int | varchar | int | ...
... | ... | 2 | ...
... | ... | 19 | ...
... | ... | 82 | ...
... | ... | 3 | ...
... | ... | 19 | ...
... | ... | 3 | ...
... | ... | 18 | ...
... | ... | 3 | ...
... | ... | 55 | ...
... | ... | 3 | ...
... | ... | 16 | ...
... | ... | 16 | ...
... | ... | 16 | ...
... | ... | 16 | ...
... | ... | 16 | ...
... | ... | 16 | ...
... | ... | 112 | ...
... | ... | 3 | ...
... | ... | 3 | ...
... | ... | 3 | ...
... | ... | 48 | ...
... | ... | 16 | ...
... | ... | 16 | ...
... | ... | 64 | ...
... | ... | -1 | ...
... | ... | 3 | ...
where is OldThing.Flags
checked and generated in the application as a bitwise and constant value defined in the application.
I'm trying to migrate to a better, more normalized database with the following three tables:
Thing
id | Value | ...
int | varchar |
FlagDetail
id | description | mask
int | varchar | int
Flag
ThingID | FlagID
int | int
I tried to generate values for the FlagDetail table with a Join
custom one IEqualityComparer
, but it returned very few results that I wanted:
void Main()
{
var flags = OldThings
.ToArray()
.Join(FlagDetails, thing=>thing.Flags.Value,
flag => flag.Mask, (t,f) => new {t, f},
new BitwiseComparer())
.Select (r => new Flag{ThingID = r.t.Id, FlagId = r.f.Id});
Flags.InsertOnSubmit(flags);
SubmitChanges();
}
// Define other methods and classes here
class BitwiseComparer : IEqualityComparer<int>
{
public bool Equals(int a, int b)
{
return (a&b)>0;
}
public int GetHashCode(int n)
{
return 0;
}
}
This resulted in 19 results, while the expected number of rows should be 29 (calculated SubscriptionTypes.Sum(st => EmailNames.Count(n => (n.Subscriptions & st.Mask)>0));
).
In the end, I resorted to using two nested loops foreach
:
var flags = new List<Flag>();
foreach (var thing in OldThings)
{
foreach (var flag in FlagDetails)
{
if ((thing.Flag & flag.Mask) > 0)
subs.Add(new Flag{ThingId = Thing.Id, FlagId = flag.Id});
}
}
Flags.InsertAllOnSubmit(flags);
SubmitChanges();
These tables are used in several applications, so the migration will be gradual with more rows added FlagDetail
as you go.
Is there a way to generate values flags
in Linq without having to manually write loops? For some reason, I can type and launch LinqPad quickly and easily with every application porting.
I currently have rows FlagDetail
with values for Mask 1
, 2
and 4
.
source to share
if i dont understand what you need to do it is cross join and not normal join
like this:
var flags = from o in OldThings.ToArray()
from f in FlagDetails
where (o.Flags.Value & flag.Mask) > 0
select new Flag{ThingID = o.Id, FlagId = f.Id};
let's say you have 3 lines in OldThings
;
1, 'blah1', 1
2, 'blah2', 2
3, 'blah3', 3
and 2 lines in FlagDetails
;
1, 'mask1', 1
2, 'mask2', 2
your connection will give you this Flag
,;
1, 1
2, 2
3, 1
with my request you will have
1, 1
2, 2
3, 1
3, 2
source to share