如何使用asp.net实体框架在sql外键中获取数据？ [英] How to get data in an sql foreign key using asp.net entity framework?

问题描述

我正在试图将关系数据库合并到我的项目中。在我的项目中执行rdbms之前，通常我通常可以通过这段代码访问我想要的值：

  public static弦乐吉他{get;组; } 
 public static List< stringInstrumentItem> GetGuitarItems（）
 {
 List< stringInstrumentItem> list2 = new List< stringInstrumentItem>（）; 
 
 MusicStoreDBEntities obj2 = new MusicStoreDBEntities（）; 
 list2 =（from g in obj2.stringInstrumentItems where g.brand == guitar select g）.ToList（）; 
 
 
 return list2; 
 
 
 
 
 
 
现在我已经在我的sql中设置了一个外键脚本，g.brand不起作用，因为它不再包含文本值，现在它是一个名为brandId的int。为了更好地理解我的问题，这里是stringInstrumentItem表的sql脚本。这是我设置外键brandId引用品牌表和它的主键。
  SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 SET ANSI_PADDING ON 
 GO 
 CREATE TABLE [dbo]。[stringInstrumentItem]（
 [itemId] [int] NOT NULL ，
 [type] [varchar]（50）NOT NULL，
 [brandId] [int] FOREIGN KEY REFERENCES brand（brandId），
 [model] [varchar]（50） ，
 [price] [float] NOT NULL，
 [itemimage1] [varchar]（255）NULL，
 [itemimage2] [varchar]（255）NULL，
 [description ] [text] NOT NULL，
 [necktype] [varchar]（100）NOT NULL，
 [body] [varchar]（100）NOT NULL，
 [fretboard] [varchar]（ 100）NOT NULL，
 [fret] [varchar]（50）NOT NULL，
 [bridge] [varchar]（100）NOT NULL，
 [neckpickup] [varchar]（100） NOT NULL，
 [bridgepickup] [varchar]（100）NOT NULL，
 [hardwarecolor] [varchar]（50）NOT NULL，
 PRIMARY KEY CLUSTERED 
（
 [itemId] ASC 
）WITH（PAD_INDEX = OFF，STATISTICS_NOREC OMPUTE = OFF，IGNORE_DUP_KEY = 
 OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY] 
）ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
 GO 
 SET ANSI_PADDING OFF 
 
  
现在这里是品牌表。这是stringInstrumentItem引用的表。
  SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 SET ANSI_PADDING ON 
 GO 
 CREATE TABLE [dbo]。[brand]（
 [brandId] [int] NOT NULL，
 [type] [varchar]（50）NOT NULL，
 [name] [varchar]（50）NOT NULL，
 [image] [varchar]（255）NULL，
 PRIMARY KEY CLUSTERED 
（
 [brandId] 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 
 SET ANSI_PADDING OFF 
 GO 
  
我的目标是能够通过使用asp.net和实体框架的stringInstrumentItem表访问品牌表中名为字段的字段的值。我没有真正找到答案。我希望你们能帮助我。如果你在MusicStoreDBEntities中有这两个关系，你应该可以访问品牌名称，如    > g.brand.name 。
  list2 =（from obj2.stringInstrumentItems 
 where g.brand.name == guitar select g）.ToList（）; 
 ^^^^^^ 
  
否则，您必须像这样手动加入 -  
  list2 =（来自obj2.stringInstrumentItems中的g 
加入b中的obj2.brand 
中的g .brandId等于b.brandId 
其中b.name ==吉他
选择g）.ToList（）; 
  
 
I'm now trying to incorporate relational database in my project. Before the implementation of rdbms in my project, normally i would normally be able to access my desired value through this code:
public static string guitar { get; set; }
public static List<stringInstrumentItem> GetGuitarItems()
{
    List<stringInstrumentItem> list2 = new List<stringInstrumentItem>();

        MusicStoreDBEntities obj2 = new MusicStoreDBEntities();
        list2 = (from g in obj2.stringInstrumentItems where g.brand == guitar select g).ToList();


    return list2;

}
But now that i already set a foreign key in my sql script, the g.brand is not working because it does not contain a text value anymore, it is now an int called brandId. To better understand my problem, here is the sql script for stringInstrumentItem table. This is where I set the foreign key brandId referencing the brand table and its primary key.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[stringInstrumentItem](
[itemId] [int] NOT NULL,
[type] [varchar](50) NOT NULL,
[brandId] [int] FOREIGN KEY REFERENCES brand(brandId),
[model] [varchar](50) NOT NULL,
[price] [float] NOT NULL,
[itemimage1] [varchar](255) NULL,
[itemimage2] [varchar](255) NULL,
[description] [text] NOT NULL,
[necktype] [varchar](100) NOT NULL,
[body] [varchar](100) NOT NULL,
[fretboard] [varchar](100) NOT NULL,
[fret] [varchar](50) NOT NULL,
[bridge] [varchar](100) NOT NULL,
[neckpickup] [varchar](100) NOT NULL,
[bridgepickup] [varchar](100) NOT NULL,
[hardwarecolor] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
[itemId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = 
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Now here is the brand table. This is the table that stringInstrumentItem is referring to.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[brand](
[brandId] [int] NOT NULL,
[type] [varchar](50) NOT NULL,
[name] [varchar](50) NOT NULL,
[image] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
[brandId] 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
SET ANSI_PADDING OFF 
GO
My goal is to be able to access the value of a field called name in brand table through stringInstrumentItem table using asp.net and entity framework. I haven't really found answers for this. I hope you guys can help me on this one.  
解决方案
If you have proper relationship between those two inside MusicStoreDBEntities, you should be able to access brand name like g.brand.name.
list2 = (from g in obj2.stringInstrumentItems 
        where g.brand.name == guitar select g).ToList();
                     ^^^^^^
Otherwise, you will have to manually join them like this - 
list2 = (from g in obj2.stringInstrumentItems
        join b in obj2.brand
        on g.brandId equals b.brandId
        where b.name == guitar
        select g).ToList();


                        
这篇关于如何使用asp.net实体框架在sql外键中获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！