在域服务类和实体数据模型中使用Linq查询SQL视图
技术问答
231 人阅读
|
0 人回复
|
2023-09-12
|
|
Visual Studio 2008 | Silverlight 3 | SQL Server 2005 | 域服务类| 实体数据模型% J6 m8 m. H# ^8 A
我有一个数据库表 Students其中包含FirstName，LastName，Semester，ProgramOfStudy，Column等列。9 \8 [, K* O2 Q
我们的目标是回到 总计 (根据姓氏的第一个字母组学生人数)，在给定所有学生姓氏的第一个字母 学期 和+ c7 _, g( J. G5 I
Programofstudy 。. t/ O+ p: u) D6 n
我编写LINQ查询失败，我想我会尝试使用它SQL视图。
" h8 z, l7 O: Q+ X% t[ol]创建视图(将17000行细分为500行)。2 a' Z6 U7 \9 g* D! u
视图由“ LastNameStartsWith总计， AcademicPeriod”，“ ProgramCode”组成。
* n+ G* _# ]5 ^( ` k添加视图.edmx模型
4 E1 A' g; l" P编写以下查询。[/ol]作为Linq；
9 Q( r# [ o- |9 G3 wpublic IQueryable GetVw_studentAlphabetSet(int cycleID,string progCode) var query = from s in db.vw_studentAlphabetSet where ((s.programCode == progCode) && (s.academicPeriod == cycleID)) select s; return query; }当使用在MSSMS填充参数sql在运行过程中，视图将向下过滤到25行，以报告事件计数。AZ，未报告X，因为没有以X开头的姓氏。8 P8 Q' g" y: I! t! ]. ~$ o
如果在返回查询中设置断点，则正确报告实体计数为24，但每个实体相同。LastNamesStartsWith =’A’，总计= 21。
% W0 ?8 H$ }& O) Y7 h因此，过滤似乎在工作，但有错误。每个实体都应该是唯一的可用区。
) \0 f) X4 A/ n: y# k8 f1 M假如我加了.Where像这样+ N% Z. p4 {1 u
where (s.programCode == progCode) && (s.academicPeriod == cycleID) && (s.LastNameStartsWith == "B");产生正确的退货。B，35，200890，BSED-ELED-ED j7 r8 R7 [% J- P2 F5 d) ~' H
当使用Linq当我返回元素时，我会得到一个可用的元素。LastNameStartsWith =“
0 W, v$ G5 ?1 ?' m6 F3 g8 gA”，总计=21.然而，再次返回B-Z，而且不返回总数。在域服务类中，它确实报告了24个元素。他们都去了哪里。即使他们都报告了。A而且，我也应该得到23个元素。
4 V: T! @2 L, _0 F那么在使用View和Linq你应该知道一些特别的事情吗？; j W8 [ ~% |' [. ]: A
这是实体模型的设计师
: l1 H- S1 j1 J! [+ T[assembly: global::System.Data.Objects.DataClasses.EdmSchemaAttribute()]// Original file name:// Generation date: 2/21/2010PMnamespace sproc.Web /// ////There are no comments for GLADYS_2010Entities in the schema. public partial class GLADYS_2010Entities : global::System.Data.Objects.ObjectContext { /// Initializes a new GLADYS_2010Entities object using the connection string found in the 'GLADYS_2010Entities' section of the application configuration file. public GLADYS_2010Entities(): base("name=GLADYS_2010Entities","GLADYS_2010Entities") this.OnContextCreated()； /// Initialize a new GLADYS_2010Entities object. public GLADYS_2010Entities(string connectionString) : base(connectionString,"GLADYS_2010Entities") this.OnContextCreated()； /// Initialize a new GLADYS_2010Entities object. public GLADYS_2010Entities(global::System.Data.EntityClient.EntityConnection connection) : base(connection,"GLADYS_2010Entities") this.OnContextCreated()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；partial void OnContextCreated()； There are no comments for vw_studentAlphabet in the schema. public global::System.Data.Objects.ObjectQuery vw_studentAlphabet get if ((this._vw_studentAlphabet == null)) this._vw_studentAlphabet = base.CreateQuery("[vw_studentAlphabet]"); return this._vw_studentAlphabet; private global::System.Data.Objects.ObjectQuery _vw_studentAlphabet; /// There are no comments for vw_studentAlphabet in the schema. public void AddTovw_studentAlphabet(vw_studentAlphabet vw_studentAlphabet) base.AddObject("vw_studentAlphabet",vw_studentAlphabet); There are no comments for GLADYS_2010Model.vw_studentAlphabet in the schema. // //// ///academicPeriod /// programCode /// [global::System.Data.Objects.DataClasses.EdmEntityTypeAttribute(NamespaceName="GLADYS_2010Model",Name="vw_studentAlphabet")] [global::System.Runtime.Serialization.DataContractAttribute(IsReference=true)] [global::System.Serializable()] public partial class vw_studentAlphabet : global::System.Data.Objects.DataClasses.EntityObject { /// /// Create a new vw_studentAlphabet object. /// /// Initial value of academicPeriod. /// Initial value of programCode. public static vw_studentAlphabet Createvw_studentAlphabet(int academicPeriod,string programCode) { vw_studentAlphabet vw_studentAlphabet = new vw_studentAlphabet(); vw_studentAlphabet.academicPeriod = academicPeriod; vw_studentAlphabet.programCode = programCode; return vw_studentAlphabet; } /// /// There are no comments for Property LastNameStartsWith in the schema. /// [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute()] [global::System.Runtime.Serialization.DataMemberAttribute()] public string LastNameStartsWith { get { return this._LastNameStartsWith; } set { this.OnLastNameStartsWithChanging(value); this.ReportPropertyChanging("LastNameStartsWith"); this._LastNameStartsWith = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value,true); this.ReportPropertyChanged("LastNameStartsWith"); this.OnLastNameStartsWithChanged()； private string _LastNameStartsWith; partial void OnLastNameStartsWithChanging(string value); partial void OnLastNameStartsWithChanged()； There are no comments for Property Total in the schema. [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute()) [global::System.Runtime.Serialization.DataMemberAttribute()) public global::System.Nullable Total get return this._Total; set this.OnTotalChanging(value); this.ReportPropertyChanging("Total"); this._Total = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value); this.ReportPropertyChanged("Total"); this.OnTotalChanged()； private global::System.Nullable _Total; partial void OnTotalChanging(global::System.Nullable value); partial void OnTotalChanged()； There are no comments for Property academicPeriod in the schema. [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(EntityKeyProperty=true,IsNullable=false)] [global::System.Runtime.Serialization.DataMemberAttribute()] public int academicPeriod get return this._academicPeriod; set this.OnacademicPeriodChanging(value); this.ReportPropertyChanging("academicPeriod"); this._academicPeriod = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value); this.ReportPropertyChanged("academicPeriod"); this.OnacademicPeriodChanged()； private int _academicPeriod; partial void OnacademicPeriodChanging(int value); partial void OnacademicPeriodChanged()； There are no comments for Property programCode in the schema. [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(EntityKeyProperty=true,IsNullable=false)] [global::System.Runtime.Serialization.DataMemberAttribute()) public string programCode get return this._programCode; set this.OnprogramCodeChanging(value); this.ReportPropertyChanging("programCode"); this._programCode = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value,false); this.ReportPropertyChanged("programCode"); this.OnprogramCodeChanged(); } } private string _programCode; partial void OnprogramCodeChanging(string value); partial void OnprogramCodeChanged(); }}这是调用域服务和实体模型的客户端代码。; j; ]- l' q# A2 d5 b: {4 N
using System;using System.Collections.Generic;using System.Linq;using System.Net;using System.Windows;using System.Windows.Controls;using System.Windows.Documents;using System.Windows.Input;using System.Windows.Media;using System.Windows.Media.Animation;using System.Windows.Shapes;using System.Windows.Ria.Data;using GLADYS.Web.GladysDataLayers;using GLADYS.Web.GladysServices;namespace GLADYS { public partial class uc_AlphabetFilter : UserControl { //data context for getting the data through the ria service. private dscStudents ctx = new dscStudents()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；public uc_AlphabetFilter() { InitializeComponent()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；； //Loaded = new RoutedEventHandler(uc_AlphabetFilter_Loaded); ctx.Load(ctx.GetVw_studentAlphabetSetQuery(200980,"BSED-ELED-ED")).Completed = new EventHandler(uc_AlphabetFilter_Completed); void uc_AlphabetFilter_Completed(object sender,EventArgs e) foreach (var q in ctx.vw_studentAlphabets) uc_AlphaButton btn = new uc_AlphaButton()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；btn.pAlphaLetter = q.LastNameStartsWith; btn.PNumber = q.Total.Value; myWrapPanel.Children.Add(btn);
! v% Z; p0 ?! `, V( o* v7 |+ f 解决方案: |
|
|
|
|
|
|
|
|
|
