0 Replies - 2042 Views - Last Post: 29 September 2010 - 03:34 PM Rate Topic: -----

#1 coultertech   User is offline

  • New D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 31
  • Joined: 18-May 09

LinqDataSource Binding with subquery where clause

Posted 29 September 2010 - 03:34 PM

I have been working with a linqdatasource and have it bound to a set of nested gridviews. I have the select of the linqdatasource overridden to a custom query, and I also have a search feature that will dynamically create the where clause to append to the LDS. Everything works except when i want to seach through names in the subquery. I'm hoping someone will know a way to do this. Here is my code, and after, I will tell you things I've tried.

LDS Selecting Override:
protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
        {
            ETDataContext etdc = new ETDataContext();
            var accts = from a in etdc.ACCOUNTs
                        orderby a.account_id
                        select new
                        {
                            Account = a.account_id,
                            aType = a.SERVICEs.FirstOrDefault().SERVICE_TYPE.service_type_desc,
                            Name = a.SERVICEs.FirstOrDefault().service_name,
                            letter_dt = a.create_dt,
                            sdate = a.SERVICEs.FirstOrDefault().service_date,
                            Amended = a.SERVICEs.FirstOrDefault().service_amended,
                            PrimAccthldr = a.PEOPLE.first_name + " " + a.PEOPLE.middle_name + " " + a.PEOPLE.last_name + a.PEOPLE.PEOPLE_TAG.tag_desc,
                            OtherAccountHolders = from oah in etdc.ACCOUNT_PEOPLE_RELATIONSHIPs
                                                  where oah.account_id == a.account_id
                                                  select new
                                                  {
                                                      relation = oah.relationship_to_primary,
                                                      oName = oah.PEOPLE.first_name + " " + oah.PEOPLE.middle_name + " " + oah.PEOPLE.last_name + oah.PEOPLE.PEOPLE_TAG.tag_desc,
                                                      pid = oah.people_id,
                                                      aid = a.account_id,
                                                      home_add = oah.PEOPLE.home_address,
                                                      home_phone = oah.PEOPLE.home_phone,
                                                      home_email = oah.PEOPLE.home_email,
                                                      home_city = oah.PEOPLE.home_city,
                                                      home_state = oah.PEOPLE.home_state,
                                                      home_zip = oah.PEOPLE.home_zip
                                                  },
                            people_id = a.primary_account_holder,
                            PrimAddress = a.PEOPLE.home_address,
                            PrimCity = a.PEOPLE.home_city,
                            PrimState = a.PEOPLE.home_state,
                            PrimZip = a.PEOPLE.home_zip

                        };
            e.Result = accts;
        }


Search Code (really more of a filter):
protected void LinkButton5_Click(object sender, EventArgs e)
        {

            StringBuilder sb = new StringBuilder();
            if (txtAIDSearch.Text.Trim() != string.Empty)
                sb.Append("Account.Contains(\"" + txtAIDSearch.Text + "\")");
            if (txtNameSearch.Text.Trim() != string.Empty)
            {
                if (sb.ToString().Trim() != string.Empty)
                    sb.Append(" && Name.Contains(\"" + txtNameSearch.Text + "\")");
                else
                    sb.Append("Name.Contains(\"" + txtNameSearch.Text + "\")");
            }
            if (txtTypeSearch.Text.Trim() != string.Empty)
            {
                if (sb.ToString().Trim() != string.Empty)
                    sb.Append(" && aType.Contains(\"" + txtTypeSearch.Text + "\")");
                else
                    sb.Append("aType.Contains(\"" + txtTypeSearch.Text + "\")");
            }
            //
            //
            //
            //TODO: THis needs to be changed to OTHER ACCOUNT HOLDERS NOT PRIMARY ACCOUNT HOLDER.  THIS HAS BEEN CHANGED.
            //
            //
            //
            if (txtPrimAcctHldr.Text.Trim() != string.Empty)
            {
                if (sb.ToString().Trim() != string.Empty)
                    sb.Append(" && PrimAccthldr.Contains(\"" + txtPrimAcctHldr.Text + "\")");
                else
                    sb.Append("PrimAccthldr.Contains(\"" + txtPrimAcctHldr.Text + "\")");
            }
            if (DropDownList1.SelectedValue.ToString() != "None")
            {
                if(DropDownList1.SelectedValue.ToString() == "Before")
                {
                    if (txtAmended.Text.Trim() != "")
                    {
                        if (sb.ToString().Trim() != string.Empty)
                            sb.Append(" && Amended < DateTime.Parse(\"" + txtAmended.Text + "\")");
                        else
                            sb.Append("Amended < DateTime.Parse(\"" + txtAmended.Text + "\")");
                    }
                }
                else if (DropDownList1.SelectedValue.ToString() == "After")
                {
                    if (txtAmended.Text.Trim() != "")
                    {
                        if (sb.ToString().Trim() != string.Empty)
                            sb.Append(" && Amended > DateTime.Parse(\"" + txtAmended.Text + "\")");
                        else
                            sb.Append("Amended > DateTime.Parse(\"" + txtAmended.Text + "\")");
                    }
                }
                else if(DropDownList1.SelectedValue.ToString() == "Never")
                {
                    
                        if (sb.ToString().Trim() != string.Empty)
                            sb.Append(" && Amended == NULL");
                        else
                            sb.Append("Amended == NULL");
                    
                }
            }
            LinqDataSource1.Where = sb.ToString();
            GridView1.DataBind();

        }


Instead of searching PrimAcctHldr.Contains I want to do something like OtherAccountHolders.oname.contains(). This does not work, I've tried it. I've also tried just oname.contains. I am really trying to do this without changing the the structure of the select query. If anyone has any ideas please shoot them my way.

Is This A Good Question/Topic? 0
  • +

Page 1 of 1