I had a ugly problem and wrote an ugly query.
Query creates a cache that is used to get person id when I have {full_name, id_code?, birth_date?}. By question mark I mean that it is perfectly valid for person not to have {id_code, birth_date} entered.
var searchvalue1 = String.Format("{0} {1}", ma.code, ma.name);
var searchvalue2 = String.Format("{0} {1}", date, ma.name);
var searchvalue3 = String.Format("{0} {1}", "?", ma.name);
if (knowledgeBase.Person.ContainsKey(searchvalue1))
return knowledgeBase.Person[searchvalue1];
if (knowledgeBase.Person.ContainsKey(searchvalue2))
return knowledgeBase.Person[searchvalue2];
if (knowledgeBase.Person.ContainsKey(searchvalue3))
return knowledgeBase.Person[searchvalue3];
Following query creates a local cache to recognize if person exists in database.
using (var ctx = new Entities())
{
//pretable cuts whole data query time from about 130 to 4 seconds
//adding BIRTH_DATE format bumps it to 9 seconds
var q0 = ctx.PERSON.Select(o => new
{
o.ID,
o.STATUS,
o.ID_CODE,
o.FIRST_NAME,
o.NAME,
o.BIRTH_DATE,
o.DATE_CREATED
})
.Where(o => (o.STATUS == "korras"))
.OrderByDescending(o => o.ID)
.ToList();
//can have duplicates, if db contains illegal entities or multinational people
var q1 = q0.Where(o => (o.ID_CODE != null))
.ToLookup(
o =>
(String.Format("{0} {1} {2}", o.ID_CODE,
o.FIRST_NAME, o.NAME)), o => o.ID)
.ToDictionary(o => o.Key, o => o.First());
//can have duplicates, if person has same name (ID_CODE == null)
var q2 = q0.Where(
o => (o.ID_CODE == null && o.BIRTH_DATE != null))/*
.OrderBy(x => x.NAME)
.ThenBy(x => x.FIRST_NAME)
.ThenByDescending(x => x.DATE_CREATED)*/
.ToLookup(
o =>
(String.Format("{0} {1} {2}",
o.BIRTH_DATE.Value.ToString("dd-MMM-yy", cultureinfo).ToUpper(),
o.FIRST_NAME, o.NAME)),
o => o.ID)
.ToDictionary(o => o.Key, o => o.First());
//can have duplicates, if person has same name (ID_CODE == null && BIRTH_DATE == null)
var q3 = q0.Where(
o => (o.ID_CODE == null && o.BIRTH_DATE == null))/*
.OrderBy(x => x.NAME)
.ThenBy(x => x.FIRST_NAME)
.ThenByDescending(x => x.DATE_CREATED)*/
.ToLookup(
o =>
(String.Format("{0} {1} {2}", "?", o.FIRST_NAME,
o.NAME)), o => o.ID)
.ToDictionary(o => o.Key, o => o.First());
var result = new Dictionary<string, decimal>();
foreach (var item in q1)
result.Add(item.Key, item.Value);
foreach (var item in q2)
result.Add(item.Key, item.Value);
foreach (var item in q3)
result.Add(item.Key, item.Value);
Person = result;
}
Notes:
Following is commented out because at some point I figured out it is not going to be unique and I can archive the effect with .OrderByDescending(o => o.ID)
.
.OrderBy(x => x.NAME)
.ThenBy(x => x.FIRST_NAME)
.ThenByDescending(x => x.DATE_CREATED)
If concatenating string as key seems odd - it is. However, as I had full name so at some point I had to add 2 parameters together anyways. Not sure if this would be better:
.ToLookup(
o => Tuple.Create(o.ID_CODE,String.Format("{0} {1}",o.FIRST_NAME, o.NAME)),
o => o.ID)
Most likely I can skip ToLookup part, but for some reason I could not wrap my head around it.
I can make the code shorter, but that is not better
using (var ctx = new Entities())
{
var q0 = ctx.PERSON.Select(o => new {
o.ID, o.STATUS, o.ID_CODE, o.FIRST_NAME,o.NAME, o.BIRTH_DATE,o.DATE_CREATED})
.Where(o => (o.STATUS == "korras"))
.OrderByDescending(o => o.ID)
.ToList();
Person = (q0.Where(o => (o.ID_CODE != null))
.ToLookup(
o => (String.Format("{0} {1} {2}", o.ID_CODE, o.FIRST_NAME, o.NAME)),
o => o.ID)
.ToDictionary(o => o.Key, o => o.First()))
.Union(
q0.Where(
o => (o.ID_CODE == null && o.BIRTH_DATE != null))
.ToLookup(
o => (String.Format("{0} {1} {2}", o.BIRTH_DATE.Value.ToString("dd-MMM-yy",
cultureinfo).ToUpper(), o.FIRST_NAME, o.NAME)),
o => o.ID)
.ToDictionary(o => o.Key, o => o.First())
).Union(
q0.Where(
o => (o.ID_CODE == null && o.BIRTH_DATE == null))
.ToLookup(
o => (String.Format("{0} {1} {2}", "?", o.FIRST_NAME, o.NAME)),
o => o.ID)
.ToDictionary(o => o.Key, o => o.First())
).ToDictionary(k => k.Key, v => v.Value);
}