Unique items in GridViewComboBoxColumn for One to One relation

[rent]

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

[Emanuel Varga]

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

[rent]

Thanks for reply Emanuel!
Can you provide sample code of your solution to achieve this?
Thanks.
 

[Emanuel Varga]

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

[rent]

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.

[rent]

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."}

[rent]

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

[Emanuel Varga]

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

[rent]

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.

[rent]

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

[Julian Benkov]

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

[rent]

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?

[Julian Benkov]

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