none
Problem with very special LINQ-to-SQL Query RRS feed

  • Question

  • Hey folks

    usually I'm finding what I am searching for here, but this time a have a really sepcific question. So it's time for my first post here Wink

    I'm pretty new to LINQ and I have really not understood everything, yet. I'm right now programming a little WPF application where I can be tested in Spanish vocabulary. For the testing purpose I need two tables in my Microsoft SQL Server 2008 database. The first ist the table A_WORD, in which all words and their translation is stored. The primary key of this table is WORD_ID. The second table is A_WORD_TEST, which I use to store when I've been asked for a word and if this asking was answered correctly or not. Important fields are

    WOTE_CREATED_DATE (datetime): This stores when I've been asked for the word
    WOTE_WORD_ID (integer): This is the WORD_ID of table A_WORD
    WOTE_CORRECT (bit): This tells if the answer I've givin was correct

    I try to do the following SQL Query in LINQ

    SELECT WORD_ID 
    FROM A_WORD LEFT OUTER JOIN A_WORD_TEST 
    ON WORD_ID = WOTE_WORD_ID
    GROUP BY WORD_ID
    /* The following is the part I really have problems with */
    ORDER BY SUM(CONVERT(decimal, isnull(WOTE_CORRECT, 0))) / COUNT(WORD_ID) ASC, MAX(WOTE_CREATED_DATE) ASC
    Could anybody of you please give me a hint how to achieve this ordering?
    Tuesday, September 15, 2009 6:01 PM

Answers

  • Hi schlonzo,

    Generally speaking, you can achieve the final Linq statement step by step when you are confronted with a sophisticated SQL statement like yours. Drop the idea that you must get to the point in one Linq statement at one time. Break your desired Linq statement into several ones, each of which finishes a relatively small objective. Finally, put these Linq statements together. Then you get the final Linq statement you want.

    I'll take your case for example to illustrate what to do when meeting difficult SQL queries.

                var results = from a in dc.A_WORD
                              join t in dc.A_WORD_TEST
                              on a.WORD_ID equals t.WOTE_WORD_ID
                              into test
                              from t in test.DefaultIfEmpty()
                              select new { WORD_ID = a.WORD_ID, WOTE_CORRECT = t.WOTE_CORRECT == false ? 0 : 1, WOTE_CREATED_DATE = t.WOTE_CREATED_DATE };
                var result = from t in dc.A_WORD_TEST
                             select new{WOTE_WORD_ID = t.WOTE_WORD_ID,WOTE_CORRECT = t.WOTE_WORD_ID==null?0:1,WOTE_CREATED_DATE = t.WOTE_CREATED_DATE};
                var result1 =
                                  (from t in dc.A_WORD_TEST
                                   select new { WOTE_WORD_ID = t.WOTE_WORD_ID }).GroupBy(p => p.WOTE_WORD_ID).Select(p => new { WOTE_WORD_ID = p.Key });
                              
                              
                var result2 = from tt in result1
                              select new { WOTE_WORD_ID = tt.WOTE_WORD_ID, Order1 = (from t in results where t.WORD_ID == tt.WOTE_WORD_ID select t.WOTE_CORRECT).Sum() /(decimal)(from t in results where t.WORD_ID == tt.WOTE_WORD_ID select t.WORD_ID).Count(), WOTE_CREATE_DATE = (from rr in result where rr.WOTE_WORD_ID == tt.WOTE_WORD_ID select rr.WOTE_CREATED_DATE).Max() };
    
                var final_result =from r5 in( (from r1 in (from r in dc.A_WORD select new {WORD_ID = r.WORD_ID})
                                   join r2 in result2
                                   on r1.WORD_ID equals r2.WOTE_WORD_ID into r3
                                   from r4 in r3.DefaultIfEmpty()
                                   select new { WORD_ID = r1.WORD_ID, ORDER1 = r4.Order1 == null ? 0 : r4.Order1, WOTE_CREATE_DATE = r4.WOTE_CREATE_DATE }).OrderBy(p=>p.ORDER1).ThenBy(p => p.WOTE_CREATE_DATE).Select(p =>p))
                                      select r5.WORD_ID;
                
                              
                foreach (var r in final_result)
                {
                    Console.WriteLine(r);
                }


    Well, the linq statements I give you seem to be a little bit longer than you probably imagined, and they're not concise because I didn't put them together. However, they clearly show you what you can do when you need to transform a difficult SQL statement – just bread it down one by one.

    Test the code in your project to see if it meets your needs. If so, put them together. Have a try!


    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Zhipeng Lee Monday, September 21, 2009 1:44 AM
    Friday, September 18, 2009 8:52 AM