Linq Query

Ramco Ramco

I am trying below query. I want only those books from t2 which have BookRpl = 4 and does not exists in View_SessionBookPlanningRecommendations for students (1,17,18)

                    LMSDBDataContext context = new LMSDBDataContext();
                    var objResult = (from t in context.View_SessionBookPlanningRecommendations
                                     from t2 in context.View_BookDetails
                                     from t3 in context.StudentDetails
                                     where (int)t.BookID == (int)t2.ID
                                     && t2.BookRPL == "4"
                                     
                                     && t.BookID != 65
                                     orderby t.Name
                                     select new
                                     {
                                         t.GroupName,
                                         t.SessionDate,
                                         t.BookTitle,
                                         t.MentorName,
                                         t.Name
                                     }).ToList();

Thanks

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramco Ramco3y ago

It is giving error - cannot convert from int? to string

List StudentCollectionList = new List();
                BALGroupParticipant bALGroupParticipant = new BALGroupParticipant();
                if (GroupCollection.Count > 0)
                {
                    List Result = bALGroupParticipant.GetGroupParticipantList(GroupCollection);
                    if (Result != null)
                    {
                        foreach (var colum in Result)
                        {
                            StudentCollectionList.Add(colum.StudentID.ToString());
                        }
                    }
                }

Thanks

Naimish Makwana3y ago

Use this

LMSDBDataContext context = new LMSDBDataContext();

List StudentCollectionList = new List();

// code to populate StudentCollectionList

var objResult = (from t2 in context.View_BookDetails
                 where t2.BookRPL == "4" &&
                 !context.View_SessionBookPlanningRecommendations
                    .Any(r => r.BookID == t2.ID && StudentCollectionList.Select(int.Parse).Contains(r.StudentID))
                 orderby t2.BookTitle
                 select new
                 {
                     t2.BookTitle,
                     t2.Author,
                     t2.Publisher
                 }).ToList();

Ramco Ramco3y ago

Hi Naimish

How i can use studentcollectionlist in Linq Query

List StudentCollectionList = new List();

                BALGroupParticipant bALGroupParticipant = new BALGroupParticipant();
                if (GroupCollection.Count > 0)
                {
                    List Result = bALGroupParticipant.GetGroupParticipantList(GroupCollection);
                    if (Result != null)
                    {
                        foreach (var colum in Result)
                        {
                            
                            StudentCollectionList.Add(colum.StudentID.ToString());
                        }
                    }
                }

Thanks

Naimish Makwana3y ago

Try below:

LMSDBDataContext context = new LMSDBDataContext();

int[] studentIds = new int[] { 1, 17, 18 }; // dynamic student IDs

var objResult = (from t2 in context.View_BookDetails
                 where t2.BookRPL == "4" &&
                 !context.View_SessionBookPlanningRecommendations
                    .Any(r => r.BookID == t2.ID && studentIds.Contains(r.StudentID))
                 orderby t2.BookTitle
                 select new
                 {
                     t2.BookTitle,
                     t2.Author,
                     t2.Publisher
                 }).ToList();

In this modified query, we first declare a collection of student IDs (in this case, an array of integers). Then we use the Contains method with the studentIds collection to check if the book has a recommendation for any of the specified students. Note that the studentIds collection can be populated dynamically based on user input or other requirements.

Thanks

Ramco Ramco3y ago
Hi Naimish

Student Id will be dynamic.

Thanks
0

Naimish Makwana3y ago

Try below query:

LMSDBDataContext context = new LMSDBDataContext();

var objResult = (from t2 in context.View_BookDetails
                 where t2.BookRPL == "4" &&
                 !context.View_SessionBookPlanningRecommendations
                    .Any(r => r.BookID == t2.ID && (r.StudentID == 1 || r.StudentID == 17 || r.StudentID == 18))
                 orderby t2.BookTitle
                 select new
                 {
                     t2.BookTitle,
                     t2.Author,
                     t2.Publisher
                 }).ToList();

Thanks