lightswitch创建没有主/外键关系的查找列表 [英] lightswitch create lookup list with no primary/foreign key relationship

问题描述

公司，   联系方式， 和国家/地区.   Corporate表具有从另一个系统导入的CorporateID，因此主键简称为ID.   CorporateID设置为唯一键，并且与联系人表中的同一列存在外部关系.我已经创建了一个LightSwitch 它之上的应用程序，并试图创建一个countrycode查找.即在新的公司"屏幕上，对于国家/地区代码 字段进行查找 列表来自国家/地区表.

I've created a simple contact database with three tables, Corporate, Contact, and Country.  The Corporate table has a CorporateID which is imported from another system, so the primary key is called simply ID.  CorporateID is set to unique key and there is a foreign relationship to the same column in Contact table. I've created a LightSwitch application on top of it and am trying to create a countrycode lookup. i.e. on the new Corporate screen, for countrycode field have a lookup list coming from the country table.

我怎样才能做到这一点?如果我正在编写SQL查询，则纯粹是:

How can I do this? If I was writing a SQL query it would purely be:

SELECT CountryCode 
FROM Country  

In the query designer it makes you create filters, I don't want to add any filters! Also, I've created foreign keys, unique key constraints but LightSwitch doesn't seem to recognise them and complained when I was importing my tables?

下面的错误

表定义: 

CREATE TABLE [dbo].[Corporate](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CorporateID] [int] NOT NULL,
	[Name] [nvarchar](50) NULL,
	[FilePrefix] [nvarchar](50) NULL,
	[DateAdded] [datetime] NULL,
	[CountryCode] [nchar](2) NOT NULL,
	[Active] [bit] NULL,
	[Product] [nvarchar](50) NULL,
	[SharePointLink] [nvarchar](255) NULL,
 CONSTRAINT [PK_Corporate] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_Corporate] UNIQUE NONCLUSTERED 
(
	[CorporateID] 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].[Corporate]  WITH CHECK ADD  CONSTRAINT [FK_Corporate_Country] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Country] ([CountryCode])
GO

ALTER TABLE [dbo].[Corporate] CHECK CONSTRAINT [FK_Corporate_Country]
GO


CREATE TABLE [dbo].[Contacts](
	[ContactID] [int] IDENTITY(1,1) NOT NULL,
	[CorporateID] [int] NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[Salutation] [nvarchar](50) NULL,
	[KeyResponsibility] [bit] NULL,
	[KeyChangeNotificationRequired] [bit] NULL,
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 
(
	[ContactID] 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].[Contacts] ADD  CONSTRAINT [DF_Contacts_KeyResponsibility]  DEFAULT ((1)) FOR [KeyResponsibility]
GO

ALTER TABLE [dbo].[Contacts] ADD  CONSTRAINT [DF__Contacts__KeyCha__1273C1CD]  DEFAULT ((1)) FOR [KeyChangeNotificationRequired]
GO

ALTER TABLE [dbo].[Contacts]  WITH CHECK ADD  CONSTRAINT [FK_Contacts_Corporate] FOREIGN KEY([CorporateID])
REFERENCES [dbo].[Corporate] ([CorporateID])
GO

ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [FK_Contacts_Corporate]
GO


CREATE TABLE [dbo].[Country](
	[CountryID] [int] IDENTITY(1,1) NOT NULL,
	[CountryCode] [nchar](2) NULL,
 CONSTRAINT [PK__Country__10D160BFD85E409C] PRIMARY KEY CLUSTERED 
(
	[CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_Country] UNIQUE NONCLUSTERED 
(
	[CountryCode] 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

推荐答案

Lightswitch(LS)在内部使用实体框架(EF)，正如您所发现的那样，有效的SQL，不会将关系映射到EF.因此，最终得到的是2个单独的表，并且EF和Lightswitch不知道这种关系 和约束条件.

Lightswitch (LS) uses Entity Framework (EF) under the hood and as you've found, while being valid SQL, doesn't map the relationship into  EF.  So, what you end up with is 2 separate tables, and EF and Lightswitch are unaware of the relationship and constraints.

我创建了一个"CreateNewCorporate"使用公司表屏幕.

I created a "CreateNewCorporate" Screen using the Corporate Table.

点击添加数据项..."；工具栏中的国家/地区"按钮，并添加了国家/地区"从查询中获取.

Clicked "Add Data Item..." button in the toolbar, and added the "Country" from the queries.

然后将国家/地区代码"更改为到自定义控件" 组合框"

Then changed the "Country Code" to a "Custom Control" "ComboBox"

在添加的代码后面

 partial void CreateNewCorporate_Activated()
        {
            IContentItemProxy c = this.FindControl("CountryCode");
            c.SetBinding(ComboBox.ItemsSourceProperty, "Screen.Countries");
            c.SetBinding(ComboBox.SelectedItemProperty, "Screen.CorporateProperty.CountryCode", BindingMode.TwoWay);
        }

请看以下内容以获取更多详细信息

Take a look at the following for some more detail

http://dotnettim.wordpress.com/2011/05/09/lightswitch-limiting-item-selections-using-a-combobox/

这篇关于lightswitch创建没有主/外键关系的查找列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！