linq query problem

Question

BOOK_ID (PK & FK) PRICE_ON_START PRICE_PLUS_COST PRICE_PLUS_EXTRA PRICE_TOTAL BOOK_ID (PK) USER_ID (FK) GROUP PHONE LOCAL_ID USER_ID (PK) NAME SURNAME SEX ISACTIVE Hello there, I have got problem, because I don't know how to get required records. So, as you can see guys I have three tables, and my question is how to get for particular USER_ID all records from Table1 where GROUP number in second table is for ex == 4. It means, that for each of the 12 groups I have different values in table1 BOOK_ID in the first and second table is the same number of course. I'm trying something like that, but this not working :( var collection1 = from a in table1 from b in table2 where a.book_id == b.book_id && a.user_id == 1 select b and now I would like to do something like that var result = from b in table2 from c in collection1 where b.book_id == c.book_id && b.group == 6 select b? Please help...

Govinda Rajulu Yemineni · Answer

Hi Nockolas, Consider three tables are: Book, Book_User, User var result = (from b in Book join bu in Book_User on b. BOOK_ID equals bu. BOOK_ID where bu. USER_ID == your userID && bu. GROUP== your GROUP number select new { bu. USER_ID , b. BOOK_ID , b. PRICE_PLUS_COST ).ToList();

Manas Mohapatra · Answer

Hi Nickolas , Consider three tables are: Book, Book_User, User If you want to get no of records for particular User ID : var result = (from b in Book join bu in Book_User on b.book_id equals bu.book_id where bu.user_id == your_userID select new { bu.user_id, b.book_name, b.price_cost).ToList(); Result: userid book_name cost 1 test 100 2 test2 200 If you want to get all users associated with books : var result = from u in User group u by new { u.user_id } into groupRes join bu in Book_User on groupRes.FirstOrDefault().user_id equals bu.user_id select new { User_ID = groupRes.FirstOrDefault().user_id, User_Name = groupRes.FirstOrDefault().name, TotalBooks = groupRes.Count() }; Result: User_ID User_Name TotalBooks 1 test_name 10 2 name2 12

Govinda Rajulu Yemineni · Answer

Hi, please refer the following URL http://www.c-sharpcorner.com/UploadFile/4b0136/editing-multiple-records-using-model-binding-in-mvc/

Manas Mohapatra · Answer

Hi, If you think that your question is solved please accept the answer is correct. Solution for next question: Here I am using EntityFramework to update records. public ActionResult Update_Books( ViewModelBook model ) { List tempList = model.books; // Using entities using(DBEntiteies test = new DBEntiteies()) { foreach(var temp in tempList) { var book = (from record in test where record.name == temp.name select record).FirstOrDefault(); // Assign all properties to entity object book.name = temp.name; test.savechanges(); } } } And one more thing you also need Book ID(Unique ID) for fetching unique from tables. From your code I am not seeing any ID properties because it is required when we use Entityframework. If you have any other unique properties then no problem. You can follow below link to resolve problem: http://stackoverflow.com/questions/23322416/passing-multiple-models-from-one-view-to-a-controller

Nickolas Novel · Answer

Thank you very much, I appreciate. I have next small problem, it would be amazing if u will have time to help. I'm trying to update many records in my books collection. I'm passing list of books to view and finally I've got in my View: Html.BeginForm() {foreach (var model in Model.Books) @Html.TextBoxFor(model=>model.name) @Html.TextBoxFor(model=>model.surname) @Html.TextBoxFor(model=>model.title) } ViewModelBook List books {get; set;} List books {get; set;} .... controller should be of course marked [httppost], but how should looks logic of controller? public ActionResult Update_Books(List books) ??? { ???? } Thanks a lot :)

linq query problem

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)