LINQ to entities notes

1. Notes

2. The database is Oracle 11g

3. LINQ to entities Technology

4. Implementation results

• Join and group
  
• Merge multiple list field values into one column
  
• Group, concatenate multiple rows of values in different value columns into one row

Public ienumerable <bookingorderdetail> getbookingorderdetailbyno (string orderno) {using (var db = new entities () {// join group (join -- Inner join, group) vaR query = from Bo in dB. booking_order join bod in dB. booking_order_detail on Bo. booking_order_id equals bod. booking_order_id where (BO. order_no.toupper () = orderno. toupper () Group new {Bo, BOD} by new {Bo. booking_order_id, Bo. local_company_name_cn, Bo. ship_name_en, Bo. voyage, Bo. status, Bo. order_no, BOD. conta_sizetype_code} into G select new {booking_order_id = G. key. booking_order_id, local_company_name_cn = G. key. local_company_name_cn, ship_name_en = G. key. ship_name_en, voyage = G. key. voyage, status = G. key. status, order_no = G. key. order_no, conta_sizetype_code = G. key. conta_sizetype_code, Count = G. select (x => X. bod. conta_sizetype_code ). count ()}; // example. The table structure is as follows: // student table // Sid sname sage // 1 piece 3 18 // 2 LI 4 19 // achievement student table // aid ctype (subject ID, foreign key) sid (foreign key -- Student ID) score (score) // 1 1 1 // 2 2 1 // 3 3 1 // 4 1 2 // 5 2 2 // column merge, merge values of multiple columns into one column var query1 = from I in query. tolist () Select New {booking_order_id = I. booking_order_id, local_company_name_cn = I. local_company_name_cn, ship_name_en = I. ship_name_en, voyage = I. voyage, status = I. status, order_no = I. order_no, conta_sizetype_code = string. join ("×", I. conta_sizetype_code, I. count)}; // execute the query block. The result // Sid sname sage ctype ccount (refer to the total number of subjects) // 1 piece, 3, 18, 1 piece, 1 piece (test a language) // 1 Zhang San 18 2 1 (taking a math test) // 1 Zhang San 18 3 1 (taking an English test) // merge rows to use ", "concatenate var query2 = from I in query1.tolist () Group I by new {I. booking_order_id, I. local_company_name_cn, I. ship_name_en, I. voyage, I. status, I. order_no} into tempgroup // select new // {// booking_order_id = tempgroup. firstordefault (). booking_order_id, // local_company_name_cn = tempgroup. firstordefault (). local_company_name_cn, // ship_name_en = tempgroup. firstordefault (). ship_name_en, // voyage = tempgroup. firstordefault (). voyage, // status = tempgroup. firstordefault (). status, // order_no = tempgroup. firstordefault (). order_no, // conta_sizetype_code = string. join (",", (tempgroup. select (S => S. conta_sizetype_code )). tolist () //}; select new bookingorderdetail () {// If the set is returned, note that the anonymous object cannot be returned. booking_order_id = 0 // omitted} must be written in this way }; // execute the query1 block, result // Sid sname sage ctype // 1 piece 3 18 1 × 1 // 1 piece 3 18 2 × 1 // 1 piece 3 18 3 × 1 return query2 ;}}

LINQ to entities notes