locked
Query 3 tables using LINQ RRS feed

  • Question

  • User251280784 posted

    Hello all

    I have a table that has a one-to-one relationship with another. The other table has a many-to-many relationship with a third table.

    salespeople <--> market

    location --> market

    When I select a location I want to get the email addresses of the salespeople for the market the location is associated with. I would like to use LINQ since it seems the cleanest code for this.

    Any suggestions on how to structure this query would be great!

    Carlos

    Tuesday, February 12, 2019 8:42 PM

All replies

  • User1120430333 posted

    Are you using Entity Framework, Linq-2-SQL or something else?

    Tuesday, February 12, 2019 9:08 PM
  • User251280784 posted

    "I would like to use LINQ since it seems the cleanest code for this."

    Tuesday, February 12, 2019 9:32 PM
  • User2053451246 posted

    "I would like to use LINQ since it seems the cleanest code for this."

    This does not answer the question.  LINQ can be used in Entity Framework, Linq-to-SQL, and just about anywhere else.

    Tuesday, February 12, 2019 9:54 PM
  • User1120430333 posted

    "I would like to use LINQ since it seems the cleanest code for this."

    Do you know anything about the two .NET technologies I am asking about?

    Tuesday, February 12, 2019 10:48 PM
  • User251280784 posted

    Yes

    Wednesday, February 13, 2019 12:13 AM
  • User1120430333 posted

    Yes

    Is it really that hard for you to answer the question on which technology you are using if any?

    Wednesday, February 13, 2019 1:58 AM
  • User251280784 posted

    Ya know, I came on asking for help. Anytime someone responds I appreciate it. But you are not only NOT being helpful, your being completely obtuse. You asked and I answered. When I initially posted the question I clearly stated I want to use LINQ. What is so difficult to understand about that?? Also, even if I don't know much about these technologies, instead of asking ridiculous questions, point people in the right direction.

    Either provide something useful or move on...

    Wednesday, February 13, 2019 8:37 PM
  • User1120430333 posted

    Ya know, I came on asking for help. Anytime someone responds I appreciate it. But you are not only NOT being helpful, your being completely obtuse. You asked and I answered. When I initially posted the question I clearly stated I want to use LINQ. What is so difficult to understand about that?? Also, even if I don't know much about these technologies, instead of asking ridiculous questions, point people in the right direction.

    Either provide something useful or move on...

    Jesus Christ, what turnip truck from a rural area did you fall off of when you made it to the city? I asked you a simple question as to what technology you are using EF, Linq-2-SQL or what, becuase  they are all different in how they use Linq. This is the nonsense you post back. What is wrong with you?

    Wednesday, February 13, 2019 9:43 PM
  • User-2054057000 posted

    "I would like to use LINQ since it seems the cleanest code for this."

    Linq is a means to iterate collection. These collections can come from Entity Framework,nHibernate or Linq to SQL. 

    You use Entity Framework which is the most popular and then tell what exactly you want to achieve.

    Thursday, February 14, 2019 12:23 PM
  • User251280784 posted

    Finally, a reasonably posed question, thank you for that.

    Yes, I am using EF then want to make the query in the controller using LINQ. I have several other functions for which I am using LINQ, so I want to keep that. I am just having a difficult time picturing this particular query...

    Thursday, February 14, 2019 1:57 PM
  • User1120430333 posted

    Finally, a reasonably posed question, thank you for that.

    Yes, I am using EF then want to make the query in the controller using LINQ. I have several other functions for which I am using LINQ, so I want to keep that. I am just having a difficult time picturing this particular query...

    You really are  some kind of a demented clown. I asked you twice what technology you were using,  as a matter of fact,   another poster asked you the same thing. And for some reason only known to you as to why you couldn't answer a simple question,  a 2 year old child could answer the question..

    Thursday, February 14, 2019 2:24 PM
  • User475983607 posted

    I am just having a difficult time picturing this particular query...

    The thing is... this type of query is very simple.  "dot" syntax is used to drill into the navigation properties.  Having difficulty navigating through the properties might indicate a design bug. Is there anyway you can provide the schema?

    The following doc illiterates different relationship configurations in EF 6. 

    https://docs.microsoft.com/en-us/ef/core/modeling/relationships

    Thursday, February 14, 2019 3:00 PM
  • User1120430333 posted

    Loading related Entities EF 6 

    https://docs.microsoft.com/en-us/ef/ef6/querying/related-data

    Query on parent, include child, child is parent to its child so do an include  for child parent's child. 

    Thursday, February 14, 2019 3:11 PM
  • User251280784 posted

    Perhaps this will help. The schema is pretty simple:

    === SALESPERSON TABLE ===

    public class ASR
    {
        [Key]
        public int AsrID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }
        public bool IsManager { get; set; }
    		
        public List<Market> Markets { get; set; }
    }
    ===

    === MARKET TABLE ===
    public class Market
    {
    	[Key]
    	public int MarketID { get; set; }
    	public string MarketName { get; set; }
    	public string MarketCode { get; set; }
    	public bool IsAssigned { get; set; }	
    		
    	public List<Asr> Asrs { get; set; }
    }
    ====================

    === LOCATION TABLE ===
    public class SSM
    {
    	[Key]
    	public int SsmID { get; set; }
    	public string SsmName { get; set; }
    		
    	public List<Market> Markets { get; set; }
    }
    ================

    I can get the markets to which the sales people are assigned, no problem there.

    What I need is when  I choose a location, I want to get the email addresses for the sales people that are assigned to the market as the location.

    Thursday, February 14, 2019 3:14 PM
  • User475983607 posted

    CarCancelo

    What I need is when  I choose a location, I want to get the email addresses for the sales people that are assigned to the market as the location.

    Your design has a many-to-many relationship between ASP and Market.  

    This will give you the a collection of Sale People which has the emails from a location and the first market.  This assumes you have the ssm is the select location.   Your query will have the DbContext and I assume a filter to get the location.  

    var email = ssm.Markets.FirstOrDefault().Asrs.Select(e => e.Email).ToList();

    You need to flatten the results otherwise you end up with an array of arrays.  I believe this will work, not test though.

    List<string> emails = ssm.Markets.SelectMany(d => d.Asrs.Select(e => e.Email)).ToList();

    Reference docs.

    https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.selectmany?view=netframework-4.7.2

    SO Post

    https://stackoverflow.com/questions/37236112/selectmany-select-flatten-a-many-to-many-relationship

    Thursday, February 14, 2019 3:40 PM
  • User251280784 posted

    I'll test that and let you know. When I get the list of emails I need do a foreach to add them to a form to send a message to all sales people in that market. So, the variable will be the one to use.

    Once I test I'll post the results here.

    Thanks for your help!

    Thursday, February 14, 2019 3:54 PM
  • User251280784 posted

    ok, so - the way I am choosing the location is by calling a JqueryUI script:

    ===

    [HttpPost]
    public JsonResult GetSSMs(string SSMNumber, int? MarketID)
    {
    var ssms = (from s in db.SSMs
    where s.SsmName.Contains(SSMNumber)
    select new { s.SsmName, s.SsmID });
    return Json(ssms, JsonRequestBehavior.AllowGet);
    }

    ===

    I have the MarketID there, not sure how I can use this to get the salespeople(ASR) in that market.

    Once I have the emails, I need to add them all to the email form:

    ===

    mail.To.Add("email");

    ===

    To this, the foreach would add a line for each email address in the variable.

    Friday, February 15, 2019 1:11 AM
  • User-1174608757 posted

    Hi CarCancelo,

    According to your description, I have made a sample on my side.Since you have got the MarketID , you could use linq to get the  corresponding Market table, then you could get the Ars in this Market,finally, you could get the Email in the Ars. Here is the demo , I hope it could help you.

    ASR.cs

     public class ASR
        {
            [Key]
            public int AsrID { get; set; }
            public string FirstName { get; set; }
            public string Email { get; set; }
    
            public List<Market> Markets { get; set; }
    
           
        }

    Market.cs:

    public class Market
        {
            [Key]
            public int MarketID { get; set; }
            public string MarketName { get; set; }
            public List<ASR> Asrs { get; set; }
        }

    linq.aspx.cs:

     protected void Page_Load(object sender, EventArgs e)
            {
                ASR A1 = new ASR();
                A1.AsrID = 1;
                A1.FirstName = "AA";
                A1.Email = "aa.com";
    
                ASR A2= new ASR();
                A2.AsrID = 2;
                A2.FirstName = "AA1";
                A2.Email = "aa1.com";
    
                Market M1 = new Market();
                M1.MarketID = 2;
                M1.MarketName = "DATA";
                M1.Asrs = new List<ASR>();
                M1.Asrs.Add(A1);
                M1.Asrs.Add(A2);
                List<Market> m1s = new List<Market>();
                m1s.Add(M1);
               
                 // get the data in Market where MarketID=2
                var email= m1s.FirstOrDefault(mbox=>mbox.MarketID == 2).Asrs.Select(en => en.Email).ToList();
    
                for (int i = 0; i < email.Count; i++)
                {
                    Response.Write(email[i] +"----");
                }

    You could see as below:

    Best Regards

    Wei Zhang

    Friday, February 15, 2019 8:29 AM
  • User475983607 posted

    This requirement is completely different than the original.  This code....

    var ssms = (from s in db.SSMs
    where s.SsmName.Contains(SSMNumber)
    select new { s.SsmName, s.SsmID });
    return Json(ssms, JsonRequestBehavior.AllowGet);

    returns a collection of SSM to JavaScript running in the browser.

    I have the MarketID there, not sure how I can use this to get the salespeople(ASR) in that market.

    See the query in my previous post!

    Once I have the emails, I need to add them all to the email form:

    ===

    mail.To.Add("email");

    ===

    Unclear.  This is code on the server?

    To this, the foreach would add a line for each email address in the variable.

    Also unclear.  Post all the relevant code and explain how you expect the code to work.  Also explain the unwanted behavior.  

    Friday, February 15, 2019 11:38 AM
  • User251280784 posted

    ok, so - I use the code below to select the SSM(location) using JqueryUI on the front end.

    ====

    var ssms = (from s in db.SSMs
    where s.SsmName.Contains(SSMNumber)
    select new { s.SsmName, s.SsmID });
    return Json(ssms, JsonRequestBehavior.AllowGet);

    ====

    Once the SSM (location) is selected, the same as what was posted, I need get the email addresses of the salespeople in the corresponding market.

    Once I have the email addresses, perhaps using a foreach loop, add them to the email addresses in the form to send the message. The email form is a standard System.Net.Mail; form, hence, the:

    ====

    MailMessage mail = new MailMessage();

    mail.To.Add("salespersonemail@domain.com");

    ====

    Friday, February 15, 2019 11:48 AM
  • User475983607 posted

    ok, so - I use the code below to select the SSM(location) using JqueryUI on the front end.

    ====

    var ssms = (from s in db.SSMs
    where s.SsmName.Contains(SSMNumber)
    select new { s.SsmName, s.SsmID });
    return Json(ssms, JsonRequestBehavior.AllowGet);
    

    ====

    Once the SSM (location) is selected, the same as what was posted, I need get the email addresses of the salespeople in the corresponding market.

    Once I have the email addresses, perhaps using a foreach loop, add them to the email addresses in the form to send the message. The email form is a standard System.Net.Mail; form, hence, the:

    ====

    MailMessage mail = new MailMessage();

    mail.To.Add("salespersonemail@domain.com");

    ====

    There is a big gap in your expatiation.  I assume jQueryUI us an autocomplete input?  Then you submit the name back to the server to get the email address?  Where is that code.

    Is there anyway you can post your code?

    Friday, February 15, 2019 11:55 AM
  • User251280784 posted

    In the controller, the autocomplete code is this:

    ======

    private readonly ApplicationDbContext db = new ApplicationDbContext();

    [HttpPost]
    public JsonResult GetSSMs(string SSMNumber, int? MarketID)
    {
    var ssms = (from s in db.SSMs
    where s.SsmName.Contains(SSMNumber)
    select new { s.SsmName, s.SsmID });
    return Json(ssms, JsonRequestBehavior.AllowGet);
    }

    ======

    On the view, the code is:

    =====

    <script type="text/javascript">
    $(document).ready(function () {
    $("#ssm").autocomplete({
    source: function (request, response) {
    $.ajax({
    url: "/Forms/GetSSMs",
    type: "POST",
    dataType: "json",
    data: { SSMNumber: request.term },
    success: function (data) {
    response($.map(data, function (item) {
    return { label: item.SsmName, value: item.SsmName };
    }))

    }
    })
    },
    messages: {
    noResults: "",
    results: function (count) {
    return count + (count > 1 ? ' results' : ' result ') + ' found';
    }
    }
    });
    })
    </script>

    =====

    Friday, February 15, 2019 12:47 PM
  • User475983607 posted

    In the controller, the autocomplete code is this:

    ======

    private readonly ApplicationDbContext db = new ApplicationDbContext();

    [HttpPost]
    public JsonResult GetSSMs(string SSMNumber, int? MarketID)
    {
    var ssms = (from s in db.SSMs
    where s.SsmName.Contains(SSMNumber)
    select new { s.SsmName, s.SsmID });
    return Json(ssms, JsonRequestBehavior.AllowGet);
    }

    ======

    On the view, the code is:

    =====

    <script type="text/javascript">
    $(document).ready(function () {
    $("#ssm").autocomplete({
    source: function (request, response) {
    $.ajax({
    url: "/Forms/GetSSMs",
    type: "POST",
    dataType: "json",
    data: { SSMNumber: request.term },
    success: function (data) {
    response($.map(data, function (item) {
    return { label: item.SsmName, value: item.SsmName };
    }))

    }
    })
    },
    messages: {
    noResults: "",
    results: function (count) {
    return count + (count > 1 ? ' results' : ' result ') + ' found';
    }
    }
    });
    })
    </script>

    =====

    You are not providing enough information for me to figure out where the holes are in your understanding.  So far all you have shown is code to populate an autocomplete.  At some point, I assume, you click a submit button and POST the location name to an action.  This action is where the Linq and email code goes. Where is that part of your code?

    Friday, February 15, 2019 1:18 PM
  • User251280784 posted

    Yes, the rest of the form is an autopopulated date/time field and 3 more text input fields. 

    Controller:

    =====

    [HttpPost]
    public ActionResult BAU(string time, string ssm, string error, string ticket, string memo, string Subject)
    {
    string userId = User.Identity.GetUserName();
    try
    {
    MailMessage mail = new MailMessage();
    mail.To.Add("email@email.com");
    mail.Priority = MailPriority.High;
    mail.From = new MailAddress(userId, "Support Team");
    mail.Subject = "BAU Error Alert For SSM#: " + ssm + " Ticket #: " + ticket;

    string userMessage = " ";
    userMessage = userMessage + "<style> td, th { border-bottom: solid 1px black; } .even { background-color: #f2f2f2 } caption { background-color: #a71010; color: white; font-size: 200%; }</style>";
    userMessage = userMessage + "<table style='width: 800px;'><caption>BAU Error Alert</caption><tbody><tr>";
    userMessage = userMessage + "<td class='even' style='width: 130px;font-weight: bold; border;'>Date/Time Submitted:</td><td class='even' style='width: 400px;font-weight: bold;'>" + time + "</td></tr>";
    userMessage = userMessage + "<td style='width: 130px;font-weight: bold; border'>SSM Number:</td><td style='width: 400px;font-weight: bold;'>" + ssm + "</td></tr>";
    userMessage = userMessage + "<td class='even' style='width: 130px;font-weight: bold; border'>Error Code:</td><td class='even' style='width: 400px;font-weight: bold;'>" + error + "</td></tr>";
    userMessage = userMessage + "<td style='width: 130px;font-weight: bold; border'>Ticket Number:</td><td style='width: 400px;font-weight: bold;'>" + ticket + "</td></tr>";
    userMessage = userMessage + "</table></tbody></tr>";
    userMessage = userMessage + "<p><small style='color: red;'>Additional Information:</small><br />" + memo + "</p>";
    string Body = userMessage + "<p><h3 style='color: red;'>We have received an alert for this machine</h3><p>Tech Support will contact the Market Team(if/when necessary).</p><p>If you have any questions please reply to this notice.</p><p>Thank you,<br />IAS Operations<br /><a href='mailto: email@email.com>Contact Support</a></p>";

    mail.Body = Body;
    mail.IsBodyHtml = true;

    SmtpClient smtp = new SmtpClient
    {
    //SMTP Server Address of gmail
    Host = "smtp.sendgrid.net",
    Port = 587,
    Credentials = new System.Net.NetworkCredential("username", "password"),
    // Smtp Email ID and Password For authentication
    EnableSsl = true
    };

    smtp.Send(mail);
    ViewBag.Message = "<br/>The Issue Has Been Reported";
    }
    catch
    {
    ViewBag.Message = "<br/>An Error Occurred. Please, Try Again!";
    }

    return View();
    }

    =====

    View

    =====

    @{
    /**/

    ViewBag.Title = "BAU Alert";
    }

    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <div class="w3-container">
    <h3 class="w3-panel w3-red w3-card-4" style="text-align: center">BAU Error Alert Notice @Html.Raw((String)ViewBag.Message)</h3>
    @using (Ajax.BeginForm("BAU", "Forms", new AjaxOptions { HttpMethod = "POST", UpdateTargetId = "BAU" }))
    {
    <div class="w3-cell-row" id="BAU">
    <div class="w3-container w3-cell">
    <p>
    <input class="w3-input w3-border" required placeholder="Machine Number" id="ssm" name="ssm">
    </p>
    <p>
    <input class="w3-input w3-border" required placeholder="Error Type" id="error" name="error">
    </p>
    <p>
    <input class="w3-input w3-border" required placeholder="Ticket Number" id="ticket" name="ticket">
    </p>
    </div>
    <div class="w3-container w3-cell w3-cell-top">
    <p>
    <input type="text" style="text-align:center;font-weight:700" readonly class="w3-input w3-border w3-border plaintext" id="time" name="time" />
    </p>
    <p>
    <!-- Message field -->
    <textarea class="w3-input w3-border" cols="50" id="memo" name="memo" rows="5" placeholder="Message/Memo"></textarea>
    </p>
    </div>
    </div>
    <p>
    <button type="submit" class="w3-btn w3-block w3-teal">Submit Information</button>
    </p>
    }
    </div>


    @section Scripts {
    <script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.12.1/jquery-ui.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function () {
    $("#ssm").autocomplete({
    source: function (request, response) {
    $.ajax({
    url: "/Forms/GetSSMs",
    type: "POST",
    dataType: "json",
    data: { SSMNumber: request.term },
    success: function (data) {
    response($.map(data, function (item) {
    return { label: item.SsmName, value: item.SsmName };
    }))

    }
    })
    },
    messages: {
    noResults: "",
    results: function (count) {
    return count + (count > 1 ? ' results' : ' result ') + ' found';
    }
    }
    });
    })
    </script>
    <script>
    $(document).ready(function () {
    DisplayCurrentTime();
    });
    function DisplayCurrentTime() {
    var dt = new Date();
    var refresh = 1000; //Refresh rate 1000 milli sec means 1 sec
    var cDate = (dt.getMonth() + 1) + "/" + dt.getDate() + "/" + dt.getFullYear();
    //document.getElementById('cTime').innerHTML = cDate + " – " + dt.toLocaleTimeString();
    document.getElementById('time').value = cDate + " – " + dt.toLocaleTimeString();
    window.setTimeout('DisplayCurrentTime()', refresh);
    }
    </script>
    }

    =====

    The models are above, in the post.

    Friday, February 15, 2019 1:30 PM
  • User475983607 posted

    I assume the "ssm" input is a unique location.  The LINQ query to get the emails is similar to the original.

    List<string> emails = db.SSMs.Select(s => s.SsmName = ssm).FirstOrDefault().Markets.SelectMany(d => d.Asrs.Select(e => e.Email)).ToList();

    Friday, February 15, 2019 2:14 PM
  • User251280784 posted

    Yes, the SSM(locations) are unique.

    In this case the "email" is a variable which comes through as a list, then use the foreach loop in the form to add the email addresses to the form(?)

    Friday, February 15, 2019 3:05 PM
  • User475983607 posted

    In this case the "email" is a variable which comes through as a list, then use the foreach loop in the form to add the email addresses to the form(?)

    Your original question is about fetching emails by location which I think has been answered. 

    What to do with the emails is up to you and the application requirements.  

    Friday, February 15, 2019 3:36 PM
  • User251280784 posted

    Yes. your help was invaluable. I re-read the messages you posted and missed something you asked when I responded.

    With this, I am not posted when the location(SSM) is chosen from the autocomplete. The email addresses should be fetched in the background, BEFORE posting the form. Once the form gets posted it simply send the mail message out. Those addresses should be added to the form prior to the post.

    Saturday, February 16, 2019 2:14 PM
  • User475983607 posted

    Yes. your help was invaluable. I re-read the messages you posted and missed something you asked when I responded.

    With this, I am not posted when the location(SSM) is chosen from the autocomplete. The email addresses should be fetched in the background, BEFORE posting the form. Once the form gets posted it simply send the mail message out. Those addresses should be added to the form prior to the post.

    The requirements become more refined as this post continues.  As far as I'm concerned your original question has been answered. 

    Now you're asking design questions which make little sense without context.  Why return email addresses from the server only to send the same email addresses back to the server?  The location name effectively designates the email addresses.

    Do you want to show the email addresses in the UI?  If so, you'll need to change the autocomplete action to include the email addresses.  Then update the HTML (DOM) from within the AJAX success handler to show the email addresses.

    Saturday, February 16, 2019 2:33 PM
  • User251280784 posted

    The original question was answered - and this is my fault for not being absolutely clear form the start.

    No, there is no showing of the email addresses. This is to add the email addresses, dynamically, to the email form so when the message is sent it is sent to the appropriate set of sales people in that particular market.

    Saturday, February 16, 2019 3:36 PM
  • User475983607 posted

    No, there is no showing of the email addresses. This is to add the email addresses, dynamically, to the email form so when the message is sent it is sent to the appropriate set of sales people in that particular market.

    Use a for each loop to If you need to send multiple emails.

    foreach(string email in emails)
    {
       //populate email
    }

    Join the array of you need to send one email to many addresses.

    var toEmails = String.Join(";", emails.ToArray());

    Saturday, February 16, 2019 5:48 PM