title: how to display new unread messages with table conditions
body: <p>i just want to load one list of new messages that founds multiple tickets and again binds them up to single repeater control. </p>
  
  <p>here i put my table schema for general idea :</p>
  
  <p><img src="https://i.stack.imgur.com/CyIH5.png" alt="enter image description here">
  and for message binding i use this code :</p>
  
  <pre><code>private void BindMessageRepeater()
      {
          DataSet myDataSet = new DataSet();
          DataTable dt = new DataTable();
          dt.Columns.Add(new DataColumn("Ref_no", typeof(string)));
          dt.Columns.Add(new DataColumn("Name", typeof(string)));
          dt.Columns.Add(new DataColumn("UserId", typeof(string)));
          dt.Columns.Add(new DataColumn("Message", typeof(string)));
          dt.Columns.Add(new DataColumn("CreatedDate", typeof(DateTime)));
          using (DataClassesDataContext db = new DataClassesDataContext())
          {
              if (db.Enquiries.Where(i =&gt; i.ForwardTo.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault()) || i.AttendBy.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault())).Count() &gt; 0)
              {
                  foreach (var enquiry in db.Enquiries.Where(i =&gt; i.ForwardTo.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault()) || i.AttendBy.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault())).ToList())
                  {
                      if (enquiry != null)
                      {
                          if (db.Messages.Where(m =&gt; m.Enquiry_History_id.Equals(enquiry.Ref_no) &amp;&amp; m.User_id.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault())).Count() &gt; 0)
                          {
                              foreach (var message in db.Messages.Where(m =&gt; m.Enquiry_History_id.Equals(enquiry.Ref_no) &amp;&amp; m.User_id.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault())).ToList())
                              {
                                  bool IsIns = true;
                                  if (message != null)
                                  {
                                      if (db.MessageFlags.Where(f =&gt; f.MessageId.Equals(message.Id) &amp;&amp; f.UserId.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault())).Count() &gt; 0)
                                      {
                                          foreach (var flag in db.MessageFlags.Where(f =&gt; f.MessageId.Equals(message.Id) &amp;&amp; !f.UserId.Equals(db.Users.Where(u =&gt; u.Username.Equals((String)Session["Username"])).Select(u =&gt; u.Ref_no).SingleOrDefault())).Select(f =&gt; new { IsRead = f.IsRead }).ToList())
                                          {
                                              if (flag.IsRead == true)
                                              {
                                                  IsIns = false;
                                              }
                                          }
                                      }
                                  }
                                  if (IsIns == true)
                                  {
                                      DataRow dr = dt.NewRow();
                                      dr["Ref_no"] = enquiry.Ref_no.ToString();
                                      dr["Name"] = db.Users.Where(u =&gt; u.Ref_no.Equals(message.User_id)).Select(u =&gt; u.FirstName + ' ' + u.LastName).SingleOrDefault().ToString();
                                      dr["UserId"] = db.Users.Where(u =&gt; u.Ref_no.Equals(message.User_id)).Select(u =&gt; u.Ref_no).SingleOrDefault().ToString();
                                      dr["Message"] = message.body.ToString();
                                      dr["CreatedDate"] = message.Created_date;
                                      dt.Rows.Add(dr);
                                  }
                              }
                          }
                      }
                  }
              }
              myDataSet.Tables.Add(dt);
              lbl_count_messages.Text = myDataSet.Tables[0].Rows.Count.ToString();
              if (myDataSet.Tables[0].Rows.Count == 0)
              {
                  message_alert.Visible = false;
              }
              Repeater_Messages.DataSource = myDataSet;
              Repeater_Messages.DataBind();
          }
      }
  </code></pre>
  
  <p>here i explain my problem in detail :</p>
  
  <p>suppose, any user/staff raise ticket/enquiry and forward to other staff then other staff can answer it. here i make one common message table all messages are stored to one table. here i just make more table like enquiry_status for checking is staff remove this ticket or replied it.</p>
  
  <p>now i just want to count new replies with checking each enquiry not have IsDisplay to false
  after that check if it's not setting to false then checks each messages and confirm check this is unread from particular user id.</p>
  
  <p>i'm try my best way from my code i 'm going wrong.</p>
  
  <p><strong>----------------------------Update------------------------------</strong></p>
  
  <p>ok lets simple :</p>
  
  <p>i 'm going with this query :</p>
  
  <pre><code>using (DataClassesDataContext db = new DataClassesDataContext())
          {
             string userid = db.Users.Where(u=&gt;u.Username.Equals((String)Session["Username"])).Select(u=&gt;u.Ref_no).SingleOrDefault();
             var query = from e in db.Enquiries.Where(i=&gt;i.AttendBy.Equals(userid) || i.ForwardTo.Equals(userid)).ToList()
                         join f in db.Flags on e.Ref_no equals f.Enquiry_History_id into joined
                         from f in joined.DefaultIfEmpty()
                         select new 
                         {
                            Ref_no = e.Ref_no,
                            IsDisplay = f.IsDisplay
                         };
              bool IsIns = true;
              foreach(var item in query)
              {
                  if(item!=null)
                  {
                      if (item.IsDisplay == false)
                      {
                          IsIns = false;
                      }
                  }
                  if (IsIns == true)
                  {
                      var query1 = from m in db.Messages.Where(m =&gt; m.Enquiry_History_id.Equals(item.Ref_no)).ToList()
                                   join f in db.MessageFlags on m.Id equals f.MessageId into joined
                                   from f in joined.DefaultIfEmpty()
                                   select new
                                   {
                                       IsRead = f.IsRead,
                                       User_id = m.User_id,
                                       Message = m.body,
                                       CreatedDate = m.Created_date
                                   };
                      bool IsIns1 = true;
                      foreach (var item1 in query1.Where(f=&gt;!f.User_id.Equals(userid)))
                      {
                          if (item1 != null)
                          {
                              if (item1.IsRead == true)
                              {
                                  IsIns1 = false;
                              }
                          }
                          if (IsIns1 == true)
                          {
                              DataRow dr = dt.NewRow();
                              dr["Ref_no"] = item.Ref_no.ToString();
                              dr["Name"] = db.Users.Where(u =&gt; u.Ref_no.Equals(item1.User_id)).Select(u =&gt; u.FirstName + ' ' + u.LastName).SingleOrDefault().ToString();
                              dr["UserId"] = db.Users.Where(u =&gt; u.Ref_no.Equals(item1.User_id)).Select(u =&gt; u.Ref_no).SingleOrDefault().ToString();
                              dr["Message"] = item1.Message.ToString();
                              dr["CreatedDate"] = item1.CreatedDate;
                              dt.Rows.Add(dr);
                          }
                      }
                  }
              }
  </code></pre>