locked
LinqToSql association RRS feed

  • Question

  • User-751080518 posted

    Hi.

    In my web application based on Dynamic Data I use LinqToSql database model (.dbml).

    I have some tables, that I can not include in sql server database diagram and draw relation.

    But that tables are actually lookup tables for some other table column value.

    So I added association in my LinqToSql model manually, but it doesn't work.

    What would be a solution for my kind of problem?

     

    Thanks in advance,

    Borut

    Monday, April 11, 2011 4:41 AM

All replies

  • User-1313990200 posted

    Can you take a screenshot of the associations and post it here? and what are the errors you are getting?

    Monday, April 11, 2011 5:31 AM
  • User-751080518 posted

    This is the image of diagram that I am using. In booth tables v_om and m_GREZNICE_GREZNICE OM fields are type varchar(255).

    Actual values in these fields are for ex. 02464838.

    I don't get any error anymore, but I don't see any values in children grid (v_om) when selecting main grid (m_GREZNICE_GREZNICE).

    Look at the code -> bottom

    Diagram

    This is the code that I'm using in my page template:

     

    protected void Page_Init(object sender, EventArgs e)
        {
            DynamicDataManager1.RegisterControl(GridView1, true);
            DynamicDataManager1.RegisterControl(FormView1, true);
    
            table = DynamicDataRouteHandler.GetRequestMetaTable(Context);
    
            GridView1.SetMetaTable(table, table.GetColumnValuesFromRoute(Context));
            FormView1.SetMetaTable(table, table.GetColumnValuesFromRoute(Context));
    
            GridDataSource.EntityTypeName = table.EntityType.AssemblyQualifiedName;
            DetailsDataSource.EntityTypeName = table.EntityType.AssemblyQualifiedName;
            if (table.EntityType != table.RootEntityType)
            {
                GridQueryExtender.Expressions.Add(new OfTypeExpression(table.EntityType));
            }
    
            if (table.HasPrimaryKey)
            {
                // setup the GridView's DataKeys
                String[] keys = new String[table.PrimaryKeyColumns.Count];
                int i = 0;
                foreach (var keyColumn in table.PrimaryKeyColumns)
                {
                    keys[i] = keyColumn.Name;
                    i++;
                }
                GridView1.DataKeyNames = keys;
            }
    
            BuildChildGrids();
    
            SetupMultiColumnSearch();
    
            GridView1.ColumnsGenerator = new FieldsGenerator(table);
        }
    
        private void BuildChildGrids()
        {
            AjaxControlToolkit.TabContainer tabContainer = new AjaxControlToolkit.TabContainer();
    
            placeholder.Controls.Clear();
            int count = 0;
            foreach (MetaColumn column in table.Columns)
            {
                //if (!column.Name.ToLower().Contains("v_om")) continue;
                if (!column.GetType().FullName.Contains("MetaChildrenColumn") && !column.GetType().FullName.Contains("MetaForeignKeyColumn")) continue;
                string childTableName = "";
                string childTableDisplayName = "";
                string paramName = "";
                string pkColumnName = "";
                string contextTypeName = "";
                string entityTypeName = "";
                MetaTable childTable = null;
                string tableName = "";
                string[] dataKeys = null;
                TypeCode tc = TypeCode.Empty;
                if (column.GetType().FullName.Contains("MetaChildrenColumn"))
                {
                    MetaChildrenColumn mcColumn = (MetaChildrenColumn)column;
                    MetaForeignKeyColumn metaForeignKeyColumn = mcColumn.ColumnInOtherTable as MetaForeignKeyColumn;
                    childTableName = mcColumn.ChildTable.Name;
                    if (childTableName.ToLower().EndsWith("_sif")) continue;
                    childTableDisplayName = mcColumn.ChildTable.DisplayName;
                    paramName = metaForeignKeyColumn.ForeignKeyNames[0];
                    pkColumnName = mcColumn.ChildTable.PrimaryKeyColumns[0].Name;
                    contextTypeName = mcColumn.ChildTable.DataContextType.FullName;
                    entityTypeName = mcColumn.ChildTable.EntityType.AssemblyQualifiedName;
                    childTable = mcColumn.ChildTable;
                    tableName = mcColumn.ChildTable.Name;
    
                    // setup the GridView's DataKeys
                    dataKeys = new String[metaForeignKeyColumn.ParentTable.PrimaryKeyColumns.Count];
                    int i = 0;
                    foreach (var keyColumn in metaForeignKeyColumn.Table.PrimaryKeyColumns)
                    {
                        dataKeys[i] = keyColumn.Name;
                        i++;
                    }
                    MetaColumn mc1 = null;
                    metaForeignKeyColumn.Table.TryGetColumn(paramName, out mc1);
                    if (mc1 != null) tc = mc1.TypeCode;
                    else tc = TypeCode.Object;
                }
                else if (column.GetType().FullName.Contains("MetaForeignKeyColumn"))
                {               
                    MetaForeignKeyColumn fkColumn = (MetaForeignKeyColumn)column;
                    if (fkColumn.ParentTable.RootEntityType.ToString().ToLower().EndsWith("sif")) continue;
                    childTableName = fkColumn.ParentTable.Name;
                    childTableDisplayName = fkColumn.ParentTable.DisplayName;
                    paramName = fkColumn.ParentTable.PrimaryKeyColumns[0].Name;
                    pkColumnName = fkColumn.ParentTable.PrimaryKeyColumns[0].Name;
                    contextTypeName = fkColumn.ParentTable.DataContextType.FullName;
                    entityTypeName = fkColumn.ParentTable.EntityType.AssemblyQualifiedName;
                    childTable = fkColumn.ParentTable;
                    tableName = fkColumn.ParentTable.Name;
    
                    // setup the GridView's DataKeys
                    dataKeys = new String[fkColumn.ParentTable.PrimaryKeyColumns.Count];
                    int i = 0;
                    foreach (var keyColumn in fkColumn.ParentTable.PrimaryKeyColumns)
                    {
                        dataKeys[i] = keyColumn.Name;
                        i++;
                    }
    
                    tc = fkColumn.ParentTable.PrimaryKeyColumns[0].TypeCode;
                }
    
                AjaxControlToolkit.TabPanel tab = new AjaxControlToolkit.TabPanel();
                tab.ID = childTableName;
                tab.HeaderText = childTableDisplayName;
    
                count++;
                GridView gv = new GridView();
                gv.DataSourceID = "LinqDataSource" + count;
                gv.ID = "myGV" + count;
                gv.AllowPaging = true;
                gv.AllowSorting = true;
                gv.CssClass = "DDGridView";
                gv.RowStyle.CssClass = "td";
                gv.HeaderStyle.CssClass = "th";
                gv.CellPadding = 2;
                gv.PagerStyle.CssClass = "DDFooter";
                gv.PagerTemplate = new GridViewPagerTemplate(this.Page);
                gv.ColumnsGenerator = new FieldsGenerator(childTable);
                gv.EmptyDataText = Resources.Strings.gridview_empty;
    
                gv.PreRender += new EventHandler(gv_PreRender);
    
                TemplateField tf = new TemplateField();
                tf.HeaderText = "";
                tf.ItemTemplate = new AddTemplateToGridView(ListItemType.Item, "", "0");
                gv.Columns.Add(tf);
    
                gv.DataKeyNames = dataKeys;
    
                LinqDataSource lds = new LinqDataSource();
                lds.ID = "LinqDataSource" + count;
                lds.ContextTypeName = contextTypeName;
                lds.TableName = tableName;
                lds.EntityTypeName = entityTypeName;
                lds.DataBinding += new EventHandler(lds_DataBinding);
                //lds.AutoGenerateWhereClause = true;
    
                ControlParameter param = new ControlParameter(paramName, GridView1.ID);
                param.Type = tc;
                param.PropertyName = "SelectedValue";
                lds.WhereParameters.Add(param);            
    
                DynamicHyperLink dynLink = new DynamicHyperLink();
                dynLink.ID = "dynInsertHyperLink" + count;
                dynLink.Action = "Insert";
                dynLink.Text = Resources.Strings.gridview_new_record;
                dynLink.TableName = childTableName;
                //dynLink.DataBinding += new EventHandler(dynLink_DataBinding);
                dynLink.NavigateUrl = "../../" + childTableName + "/Insert.aspx?actionTable=" + table.Name;            
                //GridView1.Parent.FindControl("LinqDataSource1")
    
    
                Image img = new Image();
                img.ID = "dynImg"+count;
                img.ImageUrl = "~/DynamicData/Content/Images/plus.gif";
                img.ForeColor = System.Drawing.Color.Transparent;
                img.BackColor = System.Drawing.Color.Transparent;
                img.AlternateText = Resources.Strings.gridview_new_record;
    
                Literal lit = new Literal();
                lit.ID = "dynLiteral" + count;
                lit.Text = Resources.Strings.gridview_new_record;
                
                dynLink.Controls.Add(img);
                dynLink.Controls.Add(lit);
                tab.Controls.Add(dynLink);
    
                placeholder.Controls.Add(lds);
                tab.Controls.Add(gv);
                tabContainer.Tabs.Add(tab);
                placeholder.Controls.Add(tabContainer);
    
                DynamicDataManager1.RegisterControl(gv);
                gv.SetMetaTable(childTable);
    
                headertitle.Visible = true;
            }
        }
    
        void lds_DataBinding(object sender, EventArgs e)
        {
            throw new NotImplementedException();
        }
    
        void gv_PreRender(object sender, EventArgs e)
        {
            GridView grid = (GridView)sender;
            if (grid != null)
            {
                GridViewRow pagerRow = (GridViewRow)grid.BottomPagerRow;
                if (pagerRow != null)
                {
                    pagerRow.Visible = true;
                }
            } 
        }
    
        private void SetupMultiColumnSearch()
        {
            // get multi column search attribute
            //var multiColumnSearch = table.GetAttribute<MultiColumnSearchAttribute>();
            string searchCols = "";
            foreach (var col in table.Columns)
            {
                if (col.GetType() == typeof(MetaChildrenColumn)) continue;
                //{
                //    MetaChildrenColumn mcColumn = (MetaChildrenColumn)col;
                //    foreach (var childCol in mcColumn.ChildTable.Columns)
                //    {
                //        if (!childCol.IsString || childCol.Name.ToLower()!="ime") continue;
                //        if (searchCols == "") searchCols = mcColumn.ChildTable.Name + "." + childCol.Name;
                //        else searchCols += "," + mcColumn.ChildTable.Name + "." + childCol.Name;
                //    }
                //    continue;
                //}
                if (col.IsString == false || col.GetType() == typeof(MetaForeignKeyColumn)) continue;
                if (searchCols == "") searchCols=col.Name;
                else searchCols += "," + col.Name;
            }
    
            if (!string.IsNullOrEmpty(searchCols))
            {
                var searchExpression = new SearchExpression()
                {
                    DataFields =searchCols,
                    SearchType = SearchType.Contains
                };
    
                // create control parameter
                var controlParameter = new ControlParameter() { ControlID = txbMultiColumnSearch.ID };
    
                // add control parameter to search expression
                searchExpression.Parameters.Add(controlParameter);
    
                // set context
                searchExpression.SetContext(GridQueryExtender, Context, GridDataSource);
    
                // add search expression to query extender
                GridQueryExtender.Expressions.Add(searchExpression);
    
                // make multicolumn search field set visible
                MultiSearchFieldSet.Visible = true;
            }
        }
    
        protected void Page_Load(object sender, EventArgs e)
        {
            Title = table.DisplayName;
    
            // Selection from url
            if (table.HasPrimaryKey)
            {
                GridView1.SelectedPersistedDataKey = table.GetDataKeyFromRoute();
                if (GridView1.SelectedPersistedDataKey == null)
                {
                    GridView1.SelectedIndex = 0;
                }
            }
    
            // Disable various options if the table is readonly
            if (table.IsReadOnly)
            {
                //DetailsPanel.Visible = false;
                GridView1.AutoGenerateSelectButton = false;
                GridView1.AutoGenerateEditButton = false;
                GridView1.AutoGenerateDeleteButton = false;
                GridView1.EnablePersistedSelection = false;
            }
        }
    
        void gv_RowCreated(object sender, GridViewRowEventArgs e)
        {
            SetDeleteConfirmation(e.Row);
        }
    
        protected void Label_PreRender(object sender, EventArgs e)
        {
            Label label = (Label)sender;
            DynamicFilter dynamicFilter = (DynamicFilter)label.FindControl("DynamicFilter");
            QueryableFilterUserControl fuc = dynamicFilter.FilterTemplate as QueryableFilterUserControl;
            if (fuc != null && fuc.FilterControl != null)
            {
                label.AssociatedControlID = fuc.FilterControl.GetUniqueIDRelativeTo(label);
            }
        }
    
        protected void DynamicFilter_FilterChanged(object sender, EventArgs e)
        {
            GridView1.EditIndex = -1;
            GridView1.PageIndex = 0;
            //FormView1.ChangeMode(FormViewMode.ReadOnly);
        }
    
        protected void GridView1_RowEditing(object sender, EventArgs e)
        {
            //FormView1.ChangeMode(FormViewMode.ReadOnly);
        }
    
        protected void GridView1_SelectedIndexChanging(object sender, EventArgs e)
        {
            //GridView1.EditIndex = -1;
            //string val = table.GetPrimaryKeyString(GridView1.SelectedRow);
        }
    
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {
            SetDeleteConfirmation(e.Row);
        }   
    
        private void SetDeleteConfirmation(TableRow row)
        {
            foreach (Control c in row.Cells[0].Controls)
            {
                LinkButton button = c as LinkButton;
                if (button != null && button.CommandName == DataControlCommands.DeleteCommandName)
                {
                    button.OnClientClick = "return confirm('" + Resources.Strings.gridview_delete_ask + "');";
                }
            }
        }
    
        protected void btnMultiColumnSearch_Click(object sender, EventArgs e)
        {
            var button = (Button)sender;
            if (button.ID == btnMultiColumnSearchClear.ID)
                txbMultiColumnSearch.Text = String.Empty;
        }
    
        protected void advancedsearch_Click(object sender, EventArgs e)
        {
            advancedfilter.Visible = !advancedfilter.Visible;
        }
        protected void GridView1_PreRender(object sender, EventArgs e)
        {
            GridView grid = (GridView)sender;
            if (grid != null)
            {
                GridViewRow pagerRow = (GridViewRow)grid.BottomPagerRow;
                if (pagerRow != null)
                {
                    pagerRow.Visible = true;
                }
            }
        }
    
        #region FormView Events
        protected void FormView1_ItemDeleted(object sender, FormViewDeletedEventArgs e) {
            GridView1.DataBind();
        }
    
        protected void FormView1_ItemUpdated(object sender, FormViewUpdatedEventArgs e) {
            GridView1.DataBind();
        }
    
        protected void FormView1_ItemInserted(object sender, FormViewInsertedEventArgs e) {
            GridView1.DataBind();
        }
    
        protected void FormView1_ModeChanging(object sender, FormViewModeEventArgs e) {
            if (e.NewMode != FormViewMode.ReadOnly) {
                GridView1.EditIndex = -1;
            }
        }
    
        protected void FormView1_PreRender(object sender, EventArgs e) {
            if (FormView1.Row != null) {
                SetDeleteConfirmation(FormView1.Row);
            }
        }
        #endregion
    Tuesday, April 12, 2011 4:26 AM
  • User-330204900 posted

    Hi Borutsla, is the OM column in the m_GREZNICE_GREZNICE table indexed and I assume there is no relationship in the DB?

    Tuesday, April 12, 2011 8:56 AM
  • User-330204900 posted

    Hi Borutsla, is the OM column in the m_GREZNICE_GREZNICE table indexed and I assume there is no relationship in the DB?

    Duplicate

    Tuesday, April 12, 2011 8:56 AM
  • User-751080518 posted

    Hi.

    Well I found out that my code isn't working properly.

    Part where I am using ControlParameter isn't ok. I tried with DynamicControlParameter, but also found out that when I use it, it actually uses

    SelectedPersistedDataKey of parameter name I specifie in parameter. And this works only where column in fk table is linked to primary key of main table.

    How could I make this to work?

    Tuesday, April 12, 2011 10:04 AM
  • User3866881 posted

    Hi:)

    Please correct me If I'm wrong——

    From the image you offer to us, it seems that v-om should be the master table, and the children should be m_GREZNICE_GREZNICE. So you should get your children rows like this……(However you say: I don't see any values in children grid (v_om) when selecting main grid (m_GREZNICE_GREZNICE).)

    var result = from v in LinqDataSourceContent.v_om

                    select new

                    {

                         V = v,

                        Children = v.m_GREZNICE_GREZNICE

                    };

    Wednesday, April 13, 2011 10:30 PM
  • User-751080518 posted

    This is currect, but I am using Dynamic Data.

    I managed to write this Procedure, which sets the Parameter value on Page_load and when selected index from first grid changes.

    But it doesn't work in all cases. So the question is: How can I get Values for all foreign columns in Page template for some row?

    Code:

     

    private void SetDataSourceValues(TableRow row)
        {
            int i = 1;
            GridView myGv = (GridView)placeholder.FindControl("tabContainer1").FindControl("tabPanel" + i).FindControl("myGV" + i);
            LinqDataSource lds = (LinqDataSource)placeholder.FindControl("LinqDataSource" + i);
    
            while (lds != null)
            {
                Parameter p = lds.WhereParameters[0];
    
                string where = row.GetValueForColumn(lds.TableName.Substring(0, lds.TableName.Length-1)).ToString();
                //string where = row.GetValueForColumn("m_GREZNICE_GREZNICE").ToString();
                string param = where.Split('=')[0];
                string val = where.Split('=')[1];
                
                p.Name = param;
                p.DefaultValue = val;
    
                lds.WhereParameters.Clear();
                lds.WhereParameters.Add(p);
                myGv.DataBind();
                i++;
                lds = (LinqDataSource)placeholder.FindControl("LinqDataSource" + i);
                if (lds != null)
                    myGv = (GridView)placeholder.FindControl("tabContainer1").FindControl("tabPanel" + i).FindControl("myGV" + i);
            }
        }

     

    Thursday, April 14, 2011 1:41 AM
  • User-751080518 posted

    Ok.

    Now It is working, but only if I don't hide FK columns in main grid :(

    I am hiding FK Columns with setting [ScaffoldColumn(false)] or [HideColumnIn(PageTemplate.List)].

    So I am guessing, that these columns are not in grid if I set these in meta data?

    I tought that they are only not visible.

    Is there any other way to get values of fk columns (ID VALUE) on List page?

    Thursday, April 14, 2011 5:42 AM