none
Linq to SQL - How to filter entityset with bidirectional association, recursive association RRS feed

  • Question

  • Hi,

     

    I'm fairly new to Linq to SQL. I have 2 tables in the dbml file.

     

    tblQuestion

    QuestionID                         uniqueidentifier

    QuestionText                    nvarchar (2048)

    Parent_AnswerID            uniqueidentifier              

    QuestionLevel                   smallint               

    SortIndex                            smallint               

    HideNational                      smallint               

      

    tblAnswer

    AnswerID                            uniqueidentifier              

    Parent_QuestionID         uniqueidentifier              

    AnswerText                       nvarchar(2048) 

    AnswerLevel                      smallint               

    SortIndex                            smallint               

    HideNational                      smallint               

     

    tblQuestion has a 1 to many association with tblAnswer in the dbml file where tblQuestion is the one part and tblAnswer is the many part. (tblQuestion.QuestionID = tblAnswer. Parent_QuestionID)

     tblAnswer has a 1 to many association with tblQuestion in the dbml file where tblAnswer is the one part and tblQuestion is the many part.(tblAnswer.AnswerID = tbQuestion. Parent_AnswerID)

     When I use the following query,

     int x = 0;

    var qry = from q in db.tblQuestions

              where q.QuestionLevel == x && q.HideNational != 2

              orderby q.SortIndex

              select q;

     

     

    The data brought back includes all nested EntitySets automatically. This is great.

    I would like to be able to sort and filter the nested EntitySets that are brought back.

    The real problem I run into is that I need each nested EntitySet< tblQuestion> to be filtered and sorted like the top level in the query above, but the QuestionLevel filter needs to increment by 1, so the first child entity set for tblQuestion (q.tblAnswer.tblQuestions) will have the filter q.QuestionLevel == 1 && q.HideNational != 2 and also sorted by SortIndex. Answers will need to do the same type of filtering except answers will filter by AnswerLevel, not QuestionLevel

     

    I have come close, but am still unable to do this. In the dbml’s designer.cs file, I can edit the child EntitySet to be filtered and sorted, but not the incremental filter on QuestionLevel.

     [Association(Name="tblAnswer_tblQuestion", Storage="_tblQuestions", ThisKey="AnswerID", OtherKey="Parent_AnswerID")]

    public EntitySet<tblQuestion> tblQuestions

    {

    get

    {

    return this._tblQuestions.Where(t => t.QuestionLevel == 0 && t.HideNational != 2).OrderBy(t => t.SortIndex).ToEntitySet();

    set

    {

    this._tblQuestions.Assign(value);

    }

    }

     

    I use an extension method for the .ToEntitySet() bit above.

        public static class EntityCollectionHelper

        {

            public static EntitySet<T> ToEntitySet<T>(this IEnumerable<T> source) where T : class

            {

                EntitySet<T> set = new EntitySet<T>();

                set.AddRange(source);

                return set;

            }

        }

     

    Any ideas on how I can get the incremental filter to work?

     

     

     

     

    • Moved by SamAgain Friday, July 16, 2010 3:59 AM better fit (From:.NET Base Class Library)
    Thursday, July 15, 2010 1:50 PM

Answers

  • Instead of changing the autogenerated code because yes it will autogenerate again changing whatever you did you can take advantage of the partial qualifier in the class, making those changes in your file.

    Regards

    Monday, July 19, 2010 3:15 PM
  • I think I've found an answer that works. Instead of creating another partial class and trying to move associations and so forth, I revisited the recursive method solution and was able to get past the original error I was getting. The code is below and appears to work great.

    DataClasses1DataContext db = new DataClasses1DataContext(sconnstring);

     

    var questions = from q in db.tblExplorer_Questions

                                select q;

     

    var answers = from a in db.tblExplorer_Answers

                              select a;           

     

    var qry1 = GetQuestionsofAnswer(questions.ToEntitySet(), answers.ToEntitySet(), 0, Guid.Empty);

     

    public List<Question> GetQuestionsofAnswer(EntitySet<tblExplorer_Question> questions,

                                                       EntitySet<tblExplorer_Answer> answers,

                                                       int nLevel, Guid gParentAnswerID)

    {

    List<Question> retval = null;

     

    if (!gParentAnswerID.Equals(Guid.Empty))

    {

    var qry = questions.Where(q => q.Parent_AnswerID == gParentAnswerID && q.QuestionLevel == nLevel)

    .OrderBy(q => q.SortIndex)

    .Select(q =>

    new Question

    {

    QuestionID = q.QuestionID,

    QuestionText = q.QuestionText,

    QuestionLevel = q.QuestionLevel,

    SortIndex = q.SortIndex,

    Answers = GetAnswersofQuestion(questions, answers, nLevel, q.QuestionID)

    });

     

    retval = qry.ToList<Question>();

    }

    else

    {

    var qry = questions.Where(q => q.QuestionLevel == nLevel)

    .OrderBy(q => q.SortIndex).Select(q =>

    new Question

    {

    QuestionID = q.QuestionID,

    QuestionText = q.QuestionText,

    QuestionLevel = q.QuestionLevel,

    SortIndex = q.SortIndex,

    Answers = GetAnswersofQuestion(questions, answers, nLevel, q.QuestionID)

    });

     

    retval = qry.ToList<Question>();

    }

     

               

     

     

    return retval;

    }

     

    public List<Answer> GetAnswersofQuestion(EntitySet<tblExplorer_Question> questions,

                                                     EntitySet<tblExplorer_Answer> answers,

                                                     int nLevel, Guid gParentQuestionID)

    {

    List<Answer> retval = null;

     

    int nLevelBump = nLevel + 1;

    var qry = answers.Where(a=>a.Parent_QuestionID == gParentQuestionID && a.AnswerLevel == nLevel)

    .OrderBy(a=>a.SortIndex)

    .Select(a=>

    new Answer

    {

    AnswerID = a.AnswerID,

    AnswerText = a.AnswerText,

    AnswerLevel = a.AnswerLevel,

    SortIndex = a.SortIndex,

    Questions = GetQuestionsofAnswer(questions, answers, nLevelBump, a.AnswerID)

    });

     

    retval = qry.ToList<Answer>();

     

    return retval;

    }

     

        public class Question

        {

            public Guid QuestionID

            {

                get;

                set;

            }

     

            public string QuestionText

            {

                get;

                set;

            }

     

            public int? QuestionLevel

            {

                get;

                set;

            }

     

            public int? SortIndex

            {

                get;

                set;

            }

     

            public List<Answer> Answers

            {

                get;

                set;

            }

     

        }

     

        public class Answer

        {

            public Guid AnswerID

            {

                get;

                set;

            }

     

            public string AnswerText

            {

                get;

                set;

            }

     

            public int? AnswerLevel

            {

                get;

                set;

            }

     

            public int? SortIndex

            {

                get;

                set;

            }

     

     

            public List<Question> Questions

            {

                get;

                set;

            }

     

    }

     

        public static class EntityCollectionHelper

        {

            public static EntitySet<T> ToEntitySet<T>(this IEnumerable<T> source) where T : class

            {

                EntitySet<T> set = new EntitySet<T>();

                set.AddRange(source);

                return set;

            }

    }

     

     

     

    • Marked as answer by weciii Thursday, July 29, 2010 8:39 PM
    Thursday, July 29, 2010 2:31 PM

All replies

  • Here is my latest attempt to solve the problem.

    Instead of editing the dbml designer file which I figure is bad since it is regenerated after some edits like a form’s desinger file is, I tried the code which is included below.

     

    That code gives the following error:

    Member access 'System.Guid Parent_QuestionID' of 'GridGroupingControlTest.Answer' not legal on type 'System.Collections.Generic.List`1[GridGroupingControlTest.Answer].

     

    Any idea how to get around the error?

     

     

    DataClasses1DataContext db = new DataClasses1DataContext(sconnstring);

    var qry = GetQuestionsofLevel(db, 0);

     

    private List<Answer> GetAnswersofLevel(DataClasses1DataContext db, int nlevel)

    {

    string sfield = "Questions" + nlevel.ToString();

     

    List<Answer> qry = (from a in db.tblExplorer_Answers.Where(t => t.AnswerLevel == nlevel && t.HideNational != 2)

    orderby a.SortIndex

    select new Answer

    {

    AnswerID = a.AnswerID,

    Parent_QuestionID = ((a.Parent_QuestionID == null) ? Guid.Empty : (Guid)a.Parent_QuestionID),

    Questions = GetQuestionsofLevel(db, nlevel + 1).Where(t => t.Parent_AnswerID == a.AnswerID).ToList<Question>()

    }).ToList<Answer>();

     

    return qry;

    }

     

    private List<Question> GetQuestionsofLevel(DataClasses1DataContext db, int nlevel)

    {

    List<Question> qry = (from q in db.tblExplorer_Questions.Where(t => t.QuestionLevel == nlevel && t.HideNational != 2)

    orderby q.SortIndex

    select new Question

    {

    QuestionID = q.QuestionID,

    Parent_AnswerID = ((q.Parent_AnswerID == null) ? Guid.Empty : (Guid)q.Parent_AnswerID),

    Answers = GetAnswersofLevel(db, nlevel).Where(t => t.Parent_QuestionID == q.QuestionID).ToList<Answer>()

    }).ToList<Question>();

     

     

    return qry;

    }

     

    public class Answer

    {

    public Guid AnswerID

    {

    get;

    set;

    }

     

    private Guid m_parent_questionid = Guid.Empty;

    public Guid Parent_QuestionID

    {

    get { return m_parent_questionid; }

    set { m_parent_questionid = value; }

    }

     

    public List<Question> Questions

    {

    get;

    set;

    }

    }

     

    public class Question

    {

    public Guid QuestionID

    {

    get;

    set;

    }

          

    private Guid m_parent_answerid = Guid.Empty;

    public Guid Parent_AnswerID

    {

    get { return m_parent_answerid; }

    set { m_parent_answerid = value; }

    }

     

    public List<Answer> Answers

    {

    get;

    set;

    }

    }

     

     

    Friday, July 16, 2010 4:35 PM
  • Instead of changing the autogenerated code because yes it will autogenerate again changing whatever you did you can take advantage of the partial qualifier in the class, making those changes in your file.

    Regards

    Monday, July 19, 2010 3:15 PM
  • Hi Serguey123,

    Thanks!

    I am still experimenting with that solution, but I got far enough to see that the changes aren't overwritten.

    Just wanted to update this a bit. I was able to move the associations to a partial class (DataClasses1.partial.cs) so they are no longer under threat of being automatically overwritten. After doing that, there is code that needs to be moved that was in the contructors. The constructors are located in the DataClasses1.designer.cs file. I'm not sure where to place this code. If I leave it in the designer.cs file, it will be overwritten. I do not know of a way to override the default constructor. What would be a way i could go about initializing this code in the DataClasses1.partial.cs file around the same time the constructor is called in the DataClasses1.designer.cs file? Here is the code found in one of the contructors that needs to be moved:

    this._tblExplorer_Answers = new EntitySet<tblExplorer_Answer>(new Action<tblExplorer_Answer>(this.attach_tblExplorer_Answers), new Action<tblExplorer_Answer>(this.detach_tblExplorer_Answers));

    this._tblExplorer_Answer = default(EntityRef<tblExplorer_Answer>);

    To create the partial classes I:

    • Created the associations in the designer view of the dbml file the regular way by right click Add->Association
    • Created a new file to hold the new partial classes and named it DataClasses1.partial.cs
    • Created 2 new partial classes tblExplorer_Question and tblExplorer_Answer in the file DataClasses1.partial.cs
    • Copied the association code from the original designer.cs file to the partial.cs file (Except for the code that was originally found in the contructors of the DataClasses1.designer.cs file).
    • Deleted the associations from the designer.cs file via the design view of the dbml file

     

    Users Medals

    Tuesday, July 27, 2010 11:25 AM
  • I think I've found an answer that works. Instead of creating another partial class and trying to move associations and so forth, I revisited the recursive method solution and was able to get past the original error I was getting. The code is below and appears to work great.

    DataClasses1DataContext db = new DataClasses1DataContext(sconnstring);

     

    var questions = from q in db.tblExplorer_Questions

                                select q;

     

    var answers = from a in db.tblExplorer_Answers

                              select a;           

     

    var qry1 = GetQuestionsofAnswer(questions.ToEntitySet(), answers.ToEntitySet(), 0, Guid.Empty);

     

    public List<Question> GetQuestionsofAnswer(EntitySet<tblExplorer_Question> questions,

                                                       EntitySet<tblExplorer_Answer> answers,

                                                       int nLevel, Guid gParentAnswerID)

    {

    List<Question> retval = null;

     

    if (!gParentAnswerID.Equals(Guid.Empty))

    {

    var qry = questions.Where(q => q.Parent_AnswerID == gParentAnswerID && q.QuestionLevel == nLevel)

    .OrderBy(q => q.SortIndex)

    .Select(q =>

    new Question

    {

    QuestionID = q.QuestionID,

    QuestionText = q.QuestionText,

    QuestionLevel = q.QuestionLevel,

    SortIndex = q.SortIndex,

    Answers = GetAnswersofQuestion(questions, answers, nLevel, q.QuestionID)

    });

     

    retval = qry.ToList<Question>();

    }

    else

    {

    var qry = questions.Where(q => q.QuestionLevel == nLevel)

    .OrderBy(q => q.SortIndex).Select(q =>

    new Question

    {

    QuestionID = q.QuestionID,

    QuestionText = q.QuestionText,

    QuestionLevel = q.QuestionLevel,

    SortIndex = q.SortIndex,

    Answers = GetAnswersofQuestion(questions, answers, nLevel, q.QuestionID)

    });

     

    retval = qry.ToList<Question>();

    }

     

               

     

     

    return retval;

    }

     

    public List<Answer> GetAnswersofQuestion(EntitySet<tblExplorer_Question> questions,

                                                     EntitySet<tblExplorer_Answer> answers,

                                                     int nLevel, Guid gParentQuestionID)

    {

    List<Answer> retval = null;

     

    int nLevelBump = nLevel + 1;

    var qry = answers.Where(a=>a.Parent_QuestionID == gParentQuestionID && a.AnswerLevel == nLevel)

    .OrderBy(a=>a.SortIndex)

    .Select(a=>

    new Answer

    {

    AnswerID = a.AnswerID,

    AnswerText = a.AnswerText,

    AnswerLevel = a.AnswerLevel,

    SortIndex = a.SortIndex,

    Questions = GetQuestionsofAnswer(questions, answers, nLevelBump, a.AnswerID)

    });

     

    retval = qry.ToList<Answer>();

     

    return retval;

    }

     

        public class Question

        {

            public Guid QuestionID

            {

                get;

                set;

            }

     

            public string QuestionText

            {

                get;

                set;

            }

     

            public int? QuestionLevel

            {

                get;

                set;

            }

     

            public int? SortIndex

            {

                get;

                set;

            }

     

            public List<Answer> Answers

            {

                get;

                set;

            }

     

        }

     

        public class Answer

        {

            public Guid AnswerID

            {

                get;

                set;

            }

     

            public string AnswerText

            {

                get;

                set;

            }

     

            public int? AnswerLevel

            {

                get;

                set;

            }

     

            public int? SortIndex

            {

                get;

                set;

            }

     

     

            public List<Question> Questions

            {

                get;

                set;

            }

     

    }

     

        public static class EntityCollectionHelper

        {

            public static EntitySet<T> ToEntitySet<T>(this IEnumerable<T> source) where T : class

            {

                EntitySet<T> set = new EntitySet<T>();

                set.AddRange(source);

                return set;

            }

    }

     

     

     

    • Marked as answer by weciii Thursday, July 29, 2010 8:39 PM
    Thursday, July 29, 2010 2:31 PM