none
How to use the strong type to write linq by the dynamic condition?(not use the System.Linq.Dynamic) RRS feed

  • Question

  • There is two tables in DB.one is TOHOSPITAL_TBL,the other is PATIENT_TBL.

    TOHOSPITAL_TBL contains 4 fields: TOHOSPITAL_ID,TOHOSPITAL_TIME,PATIENT_ID,UPTIME.
    PATIENT_TBL contains 5 fields: PATIENT_ID,Name,BIRTHDAY,SEX_ID,UPTIME.

    Then,the user can chose the count period (of the five choice:Year,Half a Year,Quarter,Month,Day) and count item
    (multi check item: to hospital people's age & sex) .

    when the user choose the type of count period (of the five choice:Year,Half a Year,Quarter,Month,Day) how can i made the following four method into one method by some parameter or other else(Expression<Func<T,R>>)?? any idea or impossible?

    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }
    private IEnumerable<StatisRet> cotVisitor_Time_Half(DateTime start, DateTime end)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year, Half = (tohos.TOHOSPITAL_TIME.Month - 1) / 6 }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year, Half = gs.Key.Half },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }
    private IEnumerable<StatisRet> cotVisitor_Time_Quarter(DateTime start, DateTime end)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year, Quarter = (tohos.TOHOSPITAL_TIME.Month - 1) / 3 }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year, Quarter = gs.Key.Quarter },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }
    private IEnumerable<StatisRet> cotVisitor_Time_Month(DateTime start, DateTime end)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year, tohos.TOHOSPITAL_TIME.Month }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year, Month = gs.Key.Month },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }

    Just On Life!
    Thursday, July 14, 2011 9:18 AM

Answers

All replies


  • internal class StatisRet : IComparable
    {
    internal Dates DateTile { get; set; }
    internal IEnumerable<StatisLine> Group { get; set; }
    int IComparable.CompareTo(object right)
    {
    if (!(right is StatisRet))
    throw new ArgumentException("Argument not a StatisRet",
    "right");
    StatisRet rightCustomer = (StatisRet)right;
    return CompareTo(rightCustomer);
    }


    public int CompareTo(StatisRet input)
    {
    return DateTile.getBeginDT().CompareTo(input.DateTile.getBeginDT());
    }
    }

    internal class StatisLine
    {
    internal int Id { get; set; }
    internal double Count { get; set; }
    }
    internal class Dates : CountsBase, IComparable<Dates>
    {
    internal int Quarter { get; set; }
    internal int Half { get; set; }

    public int CompareTo(Dates input)
    {
    return XXX;
    }
    internal Dates()
    {
    Day = -1;
    Year = -1;
    Month = -1;
    Quarter = -1;
    Half = -1;
    }
    }
    Just On Life!
    Thursday, July 14, 2011 9:19 AM
  • Hi Joe,

    Do you mean you want to combine the four queries into one single query?   If you want the queries to be IEnumerable<> queries, it can be easier.  Each query will be executed at the database side and return the result, and then the former query can be the input of the latter query.

    Or if you want the whole queries be executed at the database side, you will need to make the queries be IQueryable<> ones.  I am not sure it's still feasible in your scenario since the queries look quite complex.  

    If I misundertood the issue, please feel free to let me know.   I will always be here to help.  :)

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Friday, July 15, 2011 9:49 AM
    Moderator
  • Hi,Michael

    I forgot to show the type of orgVisitor(start, end).It's IQueryable<>.so it could be translated into one sql query and could be executed at the db side.~


    yes,as you described, i mean how to combine the four queries into one single query?


    Just On Life!
    Friday, July 15, 2011 2:59 PM
  • Hi Joe,

    Okay, so can you make the former query result be the input of the latter query?  It should be feasible in LINQ queries.  However, we must make the object type be specific in all the queries.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Monday, July 18, 2011 1:47 AM
    Moderator
  • Hi,Michael

    I see,the output of the query is the same,and the input is the same too.

    I think i haven't made the question clearly!~~sorry

    The difference in all of the query is

    group tohos by new { XXX} and DateTile = new Dates { XXX},so i wanna make it be the parameter of the query by some  expression or sth else.

    for example like that:

    private IEnumerable<StatisRet> cotVisitor_Time_Month(DateTime start, DateTime end,Expression1,Expression2)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { Expression1}
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Expression2},
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }




    Just On Life!
    Wednesday, July 20, 2011 5:12 AM
  • Hi Joe,

    I believe it's workable but it's better to use extention method and pass the IQueryable<StatisRet> collection into the method, e.g.

    private IQueryable<StatisRet> cotVistor_Time_Month(this query, DateTime start,....)

    {

        var q = from tohos in query ....

        return q;

    }

    Please feel free to let me know if I misunderstood the issue.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Friday, August 5, 2011 1:25 AM
    Moderator
  • Hi Joe,

    How's the problem now?

    If you need any further assistance, please feel free to let me know.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Monday, August 29, 2011 1:25 AM
    Moderator