Unique items in GridViewComboBoxColumn for One to One relation

13 posts, 0 answers
  1. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 15 Nov 2012 Link to this post

    In radGridView I've GridViewComboBoxColumn column:
    GridViewComboBoxColumn MemberColumn = new GridViewComboBoxColumn();
                MemberColumn.Name = "MemberColumn";
                MemberColumn.HeaderText = "Member";
                MemberColumn.DataSource = ctx.Members;
                MemberColumn.ValueMember = "MemberID";
                MemberColumn.DisplayMember = "MemberName";
                MemberColumn.FieldName = "MemberID";
                this.ResultGridView.Columns.Add(MemberColumn);

    In EF model I've One to One Relationship:
    Link
    I want to show in MemberColumn dropdownlist show only not added items to ResultGridView from Members Entity.
    Because user can't add twice 1 member to Data table.
    Thanks



  2. Emanuel Varga
    Emanuel Varga avatar
    1336 posts
    Member since:
    May 2010

    Posted 15 Nov 2012 Link to this post

    Hello,

    In order to achieve this, you can subscribe to the grids CellEditorInitialized event and change the datasource for the editor to show just the unassigned items + the item for the current item ( do not forget this one, otherwise you will not have any item selected when the editor opens ).

    A simple linq Where would be enough here.

    If you need more help on this one, or if you have any other questions, please let me know.

    Best Regards,
    Emanuel Varga
    WinForms MVP
  3. UI for WinForms is Visual Studio 2017 Ready
  4. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 15 Nov 2012 Link to this post

    Thanks for reply Emanuel!
    Can you provide sample code of your solution to achieve this?
    Thanks.
     
  5. Emanuel Varga
    Emanuel Varga avatar
    1336 posts
    Member since:
    May 2010

    Posted 15 Nov 2012 Link to this post

    Hello,

    Please consider the following example, it is a very basic one
    using System;
    using System.Collections.Generic;
    using System.Drawing;
    using System.Linq;
    using System.Windows.Forms;
    using Telerik.WinControls.UI;
     
    public partial class Form1 : Form
    {
        private RadGridView grid = new RadGridView();
     
        private List<GridDataSource> fullList;
        private List<GridDataSource> gridBindedList;
     
        public Form1()
        {
            InitializeComponent();
            this.Size = new Size(640, 480);
            grid.Dock = DockStyle.Fill;
            grid.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
            grid.EnableFiltering = true;
            grid.DataBindingComplete += grid_DataBindingComplete;
            grid.CellEditorInitialized += grid_CellEditorInitialized;
            this.Controls.Add(grid);
        }
     
        private void grid_CellEditorInitialized(object sender, GridViewCellEventArgs e)
        {
            if (e.Column.FieldName != "Name")
            {
                return;
            }
     
            var dropDownEditor = e.ActiveEditor as RadDropDownListEditor;
            if (dropDownEditor == null)
            {
                return;
            }
     
            var editorElement = dropDownEditor.EditorElement as RadDropDownListEditorElement;
            if (editorElement != null)
            {
                editorElement.DataSource =
                    fullList.Where(
                        f => this.gridBindedList.All(element => element.Name != f.Name) || f.Name.Equals(e.Value));
            }
        }
     
        private void grid_DataBindingComplete(object sender, GridViewBindingCompleteEventArgs e)
        {
            GridViewComboBoxColumn MemberColumn = new GridViewComboBoxColumn();
            MemberColumn.Name = "MemberColumn";
            MemberColumn.HeaderText = "Member";
            MemberColumn.DataSource = fullList;
            MemberColumn.ValueMember = "Name";
            MemberColumn.DisplayMember = "Name";
            MemberColumn.FieldName = "Name";
            grid.Columns.Add(MemberColumn);
            grid.BestFitColumns();
        }
     
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
     
            fullList = new List<GridDataSource>();
            for (int i = 0; i < 20; i++)
            {
                fullList.Add(new GridDataSource("Test" + i.ToString()));
            }
     
            gridBindedList = fullList.Take(10).ToList();
            grid.DataSource = gridBindedList;
        }
    }
     
    public class GridDataSource
    {
        public string Name { get; set; }
     
        public GridDataSource(string name)
        {
            this.Name = name;
        }
    }

    If you have any other questions, please let me know.

    Best Regards,
    Emanuel Varga
    WinForms MVP
  6. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 15 Nov 2012 Link to this post

    Well I've 2 Entities: ctx.Data and ctx.Members with One To One Relation by MemberId field. In MemberColumn dropdown list I want to show only MemberName from ctx.Members which isn't already added to ctx.Data. I try something:
    var editorElement = dropDownEditor.EditorElement as RadDropDownListEditorElement;
                if (editorElement != null)
                {
                    editorElement.DataSource =
                        ctx.Data.Where(
                            f => ctx.Members.All(element => element.MemberId != f.MemberId) || f.Members.MemberName.Equals(e.Value));
                }
    But I can't get achieve my goal. Where I mistake?
    Thanks.
  7. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 15 Nov 2012 Link to this post

    I try code:
    private void ResultGridView_CellEditorInitialized(object sender, GridViewCellEventArgs e)
            {
                if (e.Column.FieldName != "MemberID")
                {
                    return;
                }
     
                var dropDownEditor = e.ActiveEditor as RadDropDownListEditor;
                if (dropDownEditor == null)
                {
                    return;
                }
     
                var editorElement = dropDownEditor.EditorElement as RadDropDownListEditorElement;
                if (editorElement != null)
                {
                    editorElement.DataSource =
                        ctx.Members.Where(
                            f => ctx.Data.All(element => element.MemberId != f.MemberId) );
                }
            }
    And it is show in drop down list only not added to Data members, but when I select item in dropdown list it is not selected in grid cell. And when I try to save ctx.SaveChanges() I get error:
    {"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Data_Memb\". The conflict occurred in database \"Swim\", table \"dbo.Members\", column 'MemberId'.\r\nThe statement has been terminated."}
  8. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 16 Nov 2012 Link to this post

    1. When I try to select item in MemberColumn GridViewComboBoxColumn it's not show in cell after leave this cell. What I did wrong?
    2. And value from this column try inserted to dbo.Members? It's must to insert Data table.
    Thanks



  9. Emanuel Varga
    Emanuel Varga avatar
    1336 posts
    Member since:
    May 2010

    Posted 16 Nov 2012 Link to this post

    Hello again,

    Can you please try to modify my example to highlight the issues you are having? Currently i cannot use entity framework to test it like that.

    Best Regards,
    Emanuel Varga
    WinForms MVP
  10. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 16 Nov 2012 Link to this post

    Dear Emanuel!
    I can't to use as DataSource simple IList. I can create simple example with EF in VS 2010 that is similar to my project. And I can show this trouble.
  11. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 19 Nov 2012 Link to this post

    OK, I try to create simple WinForms example with my problems:
    1. Create database and 2 tables with One To One relation in SQL Server 2008 R2 Express:
    USE [master]
    GO
       
    /****** Object:  Database [RadGridViewTest]    Script Date: 11/19/2012 09:48:23 ******/
    CREATE DATABASE [RadGridViewTest] ON  PRIMARY
    ( NAME = N'RadGridViewTest', FILENAME = N'your path\RadGridViewTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON
    ( NAME = N'RadGridViewTest_log', FILENAME = N'your path\RadGridViewTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
       
    USE [RadGridViewTest]
    GO
    CREATE TABLE [dbo].[Books](
        [BookId] [int] IDENTITY(1,1) NOT NULL,
        [BookName] [nvarchar](max) NOT NULL,
     CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
    (
        [BookId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
       
    USE [RadGridViewTest]
    GO
    CREATE TABLE [dbo].[ISBNS](
        [BookId] [int] NOT NULL,
        [ISBN] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_ISBNS] PRIMARY KEY CLUSTERED
    (
        [BookId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
       
    ALTER TABLE [dbo].[ISBNS]  WITH CHECK ADD  CONSTRAINT [FK_ISBNS_Books] FOREIGN KEY([BookId])
    REFERENCES [dbo].[Books] ([BookId])
    GO
       
    ALTER TABLE [dbo].[ISBNS] CHECK CONSTRAINT [FK_ISBNS_Books]
    GO
    2. Open VS 2010 and create new WinForms .NET Framework 4 project. Add new ADO.NET Entity Data Model named TestModel.edmx and choose Generate from database, create connection to database created above, choose our 2 tables and see.
    3. Drag to form 2 RadGridView component to 2 TabControl pages. Let's this grids names are BookGridView and ISBNGridView. 
    4. So, made some changes:
    public partial class Form1 : Form
        {
            RadGridViewTestEntities ctx;
            public Form1()
            {
                InitializeComponent();
                ctx = new RadGridViewTestEntities();
     
                BookGridView.DataSource = ctx.Books;
                BookGridView.Columns["ISBNS"].IsVisible = false;
     
                ISBNGridView.DataSource = ctx.ISBNS;
                ISBNGridView.Columns["Books"].IsVisible = false;
                ISBNGridView.Columns["BookId"].IsVisible = false;
     
                GridViewComboBoxColumn bookColumn = new GridViewComboBoxColumn();
                bookColumn.Name = "bookColumn";
                bookColumn.HeaderText = "Book";
                bookColumn.DataSource = ctx.Books;
                bookColumn.ValueMember = "BookId";
                bookColumn.DisplayMember = "BookName";
                bookColumn.FieldName = "BookId";
                this.ISBNGridView.Columns.Add(bookColumn);
            }
     
            private void BookGridView_UserAddedRow(object sender, GridViewRowEventArgs e)
            {
                ctx.SaveChanges();
            }
     
            private void BookGridView_UserDeletedRow(object sender, GridViewRowEventArgs e)
            {
                ctx.SaveChanges();
            }
     
            private void BookGridView_CellEndEdit(object sender, GridViewCellEventArgs e)
            {
                ctx.SaveChanges();
            }
     
            private void ISBNGridView_CellEndEdit(object sender, GridViewCellEventArgs e)
            {
                ctx.SaveChanges();
            }
     
            private void ISBNGridView_UserDeletedRow(object sender, GridViewRowEventArgs e)
            {
                ctx.SaveChanges();
            }
     
            private void ISBNGridView_UserAddedRow(object sender, GridViewRowEventArgs e)
            {
                ctx.SaveChanges();
            }
        }
    And now we have that problem which I explain in this thread above. 
    Project file is here http://rusfolder.com/33678015
  12. Julian Benkov
    Admin
    Julian Benkov avatar
    1135 posts

    Posted 19 Nov 2012 Link to this post

    Hi Rent,

    To support all CRUD operations in this scenario, you must bind the RadGridView control to your DBSet entity object using a Local property with result converting ToBindingList. Here is a changed code snippet:
    using System.Linq;
    using System.Data.Entity;
     
    public Form1()
    {
        InitializeComponent();
        ctx = new RadGridViewTestEntities();
     
        BookGridView.DataSource = ctx.Books.Local.ToBindingList();
        BookGridView.Columns["ISBNS"].IsVisible = false;
     
        ISBNGridView.DataSource = ctx.ISBNS.Local.ToBindingList();
        ISBNGridView.Columns["Books"].IsVisible = false;
        ISBNGridView.Columns["BookId"].IsVisible = false;
     
        GridViewComboBoxColumn bookColumn = new GridViewComboBoxColumn();
        bookColumn.Name = "bookColumn";
        bookColumn.HeaderText = "Book";
        bookColumn.DataSource = ctx.Books.Local.ToBindingList();
        bookColumn.ValueMember = "BookId";
        bookColumn.DisplayMember = "BookName";
        bookColumn.FieldName = "BookId";
        this.ISBNGridView.Columns.Add(bookColumn);
    }

    More information about EntityFramework Local property and binding to WinForms you can find in this blog post

    I hope this helps. 

    Kind regards,
    Julian Benkov
    the Telerik team
    Q3’12 of RadControls for WinForms is available for download (see what's new). Get it today.
  13. rent
    rent avatar
    36 posts
    Member since:
    May 2008

    Posted 19 Nov 2012 Link to this post

    I've install EF 5.0 in my VS 2010 with nuget: Install-Package EntityFramework -Version 5.0.0
    But I can't find Local property? And how this property solve my problem with unique items in dropdownliat of RadGridView combobox column?
  14. Julian Benkov
    Admin
    Julian Benkov avatar
    1135 posts

    Posted 22 Nov 2012 Link to this post

    Hello Rent,

    Thank you for the provided information. However, it did not allow us to reproduce the case and assist you accordingly. Therefore, I would kindly ask you to open a new support ticket and attach a sample project with some part of your data. This will allow to analyze your case in depth, hence provide you with further assistance.

    Thank you for yout time and cooperation. I am looking forward to your reply. 

    Regards,
    Julian Benkov
    the Telerik team
    Q3’12 of RadControls for WinForms is available for download (see what's new). Get it today.
Back to Top
UI for WinForms is Visual Studio 2017 Ready