Refactor Oracle Entity Framework lambda query

Question

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);
}

Answer 1

Code seemed obfuscated and I cleared it up. Now code gets used as :

.Where(ma => Find.Person(ma, out person, ctx) || Insert.Person(ma, out person, ctx))

I created a method and 3 overloads

public static bool Person(managersTypeManager ma, out PERSON p, Entities ctx)
{
    var idCode = ma.code;
    var name = ma.name;
    var birthDate = DateTime.Parse(ma.person_birth_date);
    return (
        Person(idCode, out p, ctx) || 
        Person(name, birthDate, out p, ctx) || 
        Person(out p, name, ctx));
}

Id code

public static bool Person(string idCode, out PERSON response, Entities ctx)
{
    PERSON result = null;
    var success = Ui.Instance.Try(
            process: () =>
            {
                result = ctx.PERSON
                    .Where(o => (o.STATUS == "korras"))
                    .OrderByDescending(o => o.ID)
                    .FirstOrDefault(o => o.ID_CODE == idCode);
            },
        onStart: "Finding (PERSON) information based id code",
        onAfter: "Finding (PERSON) information done",
        onError: "Finding (PERSON) information failed"
    );
    response = result;
    return success && result!=null;
}

Birth date

public static bool Person(string name, DateTime birthDate, out PERSON response, Entities ctx)
{
    PERSON result = null;
    var success = Ui.Instance.Try(
            process: () =>
            {
                result = ctx.PERSON
                    .Where(o => (o.STATUS == "korras"))
                    .OrderByDescending(o => o.ID)
                    .FirstOrDefault(o => (
                        (o.NAME + o.FIRST_NAME) == name && 
                        o.BIRTH_DATE == birthDate));
            },
        onStart: "Finding (PERSON) information based name and birth date",
        onAfter: "Finding (PERSON) information done",
        onError: "Finding (PERSON) information failed"
    );
    response = result;
    return success && result != null;
}

No id code or birth date

public static bool Person(out PERSON response, string name, Entities ctx)
{
    PERSON result = null;
    var success = Ui.Instance.Try(
            process: () =>
            {
                result = ctx.PERSON
                    .Where(o => (o.STATUS == "korras"))
                    .OrderByDescending(o => o.ID)
                    .FirstOrDefault(o => ((o.NAME + o.FIRST_NAME) == name));
            },
        onStart: "Finding (PERSON) information based name",
        onAfter: "Finding (PERSON) information done",
        onError: "Finding (PERSON) information failed"
    );
    response = result;
    return success && result != null;
}