2
\$\begingroup\$

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);
}
\$\endgroup\$
  • \$\begingroup\$ I can also remove o.DATE_CREATED from pre-query. \$\endgroup\$ – Margus Jun 17 '14 at 18:19
1
\$\begingroup\$

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;
}
\$\endgroup\$
  • \$\begingroup\$ In addition, as you might notice I made cache optional. \$\endgroup\$ – Margus Jun 25 '14 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.