CODEWITHSUNDEEP
c#asp.netlinq
Vinit Bodhwani
Vinit Bodhwani

Reputation: 435

select values based on condition using .Include in c#

I want to return only those answers whose aapprove value is 1. Here is my Answer.cs :

public partial class Answer
    {
        public int aid { get; set; }
        public string abody { get; set; }
        public Nullable<System.DateTime> adate { get; set; }
        public Nullable<int> alikes { get; set; }
        public int uid { get; set; }
        public int qid { get; set; }
        public int Question_qid { get; set; }
        public Nullable<int> aapprove { get; set; }

        public virtual Question Question { get; set; }
    }

and my Question.cs :

public partial class Question
    {

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Question()
        {
            this.Answers = new HashSet<Answer>();
        }

        public int qid { get; set; }
        public string qtitle { get; set; }
        public string qbody { get; set; }
        public string qtags { get; set; }
        public Nullable<int> qlikes { get; set; }
        public Nullable<int> qcomments { get; set; }
        public int uid { get; set; }
        public Nullable<System.DateTime> qdate { get; set; }
        public int User_uid { get; set; }
        public Nullable<int> qapprove { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Answer> Answers { get; set; }
    }

In my controller, I am doing,

List<Question> questions = entities.Questions
                    .Include("Answers")
                    .Where(q => q.qapprove == 1)
                    .ToList();

This is returning array like :

 {
        "qid": 1,
        "qtitle": "What is the fees of xyz college",
        "qbody": "I wanted to know the fees of xyz MBBS college. Please help if any one knows.",
        "qtags": "FEES",
        "qlikes": 1,
        "qcomments": 14,
        "uid": 1,
        "qdate": "2017-03-12T04:35:00",
        "User_uid": 1,
        "qapprove": 1,
        "Answers": [
            {
                "aid": 1,
                "abody": "The fees of this college is not very high. Average people can manage easily.",
                "adate": "2017-01-02T04:35:00",
                "alikes": 15,
                "uid": 1,
                "qid": 1,
                "Question_qid": 1,
                "aapprove": 0
            }
        ]
    }

I want to return only those answers whose aapprove value is 1.(Include("Answers") is returning all the answers which I don't want).How can I do this?

Upvotes: 1

Views: 1614

Answers (2)

Fabio
Fabio

Reputation: 32455

You can approach it from other side

var questions = entities.Answers
                        .Where(answer => answer.aapprove == 1)
                        .GroupBy(answer => answer.Question_qid)
                        .Select(group => new {
                            Question = group.First().Question,
                            Answers = group;
                        });

Upvotes: 0

johnny 5
johnny 5

Reputation: 20995

You can use select to load you data and entity framework will take care of attaching 

var data = entities.Questions
                .Include("Answers")
                .Where(q => q.qapprove == 1)
                .Select(x => new { Questions = x, Answers = 
                                               x.Answers.Where(a => /*Your condition*/)})
                .ToList();

var questions = data.Select(x => x.Questions).ToList();

Upvotes: 1

Related Questions