在域服务类和实体数据模型中使用Linq查询SQL视图 [英] Querying a SQL View, with Linq in a Domain Services Class and Entity Data Model

查看：68 发布时间：2020/5/1 6:17:06 sql linq views ria

本文介绍了在域服务类和实体数据模型中使用Linq查询SQL视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Visual Studio 2008 | Silverlight 3 | SQL Server 2005 |域服务类|实体数据模型

我有一个数据库表"Students"，其中包含FirstName，LastName，Semester，ProgramOfStudy，Column等列.

目标是根据给定的学期中所有学生的姓氏的首字母，返回总数(按姓氏的首字母分组的学生人数) >和学习计划.

我编写LINQ查询失败，并认为我会尝试使用SQL视图.

创建视图(将17,000行细分为5000行).
视图由"LastNameStartsWith"，总计"，"AcademicPeriod"，"ProgramCode"组成.
将视图添加到.edmx模型
编写以下查询.

以Linq身份；

public 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行，报告出现次数. A-Z，未报告X，因为没有姓氏以X开头.

如果在返回查询上设置了一个断点，则它正确地报告一个实体计数为24，但是每个实体都是相同的. LastNamesStartsWith ='A'，总计= 21.

因此，看来过滤正在运行，但存在错误.每个实体都应该是唯一的A-Z.

如果我添加到.


where (s.programCode == progCode) && (s.academicPeriod == cycleID) && (s.LastNameStartsWith == "B");

生成正确的退货. B，35，200890，BSED-ELED-ED

在使用Linq返回元素时，我得到了一个可衡量的元素. LastNameStartsWith ="A"，总计=21.但是，再次返回B-Z，并且不返回总计.在域服务类中，它确实报告元素数为24.它们都去了哪里.即使它们都报告A和21，我也应该再获得23个元素.

那么在使用视图和Linq时应该知道一些特别的东西吗?

这里是实体模型的设计者


[assembly: global::System.Data.Objects.DataClasses.EdmSchemaAttribute()]

// Original file name:
// Generation date: 2/21/2010 10:25:00 PM
namespace 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();
    }
}

这是调用域服务和实体模型的客户端代码.



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);
                }
            }

询问相同的问题解决方案

使用Entity Framework处理视图时，请确保正确设置了实体键.由于视图没有主键，EF会尝试推断该键，因此它通常是不正确的，因此常常被忽略.实体键不正确会导致查询结果不一致和奇怪.

Visual Studio 2008 | Silverlight 3 | SQL Server 2005 | Domain Services Class | Entity Data Model

I have a database table "Students" with columns FirstName, LastName, Semester, ProgramOfStudy, Column etc.......

The goal is to return a Total (number of students grouped by first letter of lastname), based on the first letter of the lastname of all students in a given Semester and Programofstudy.

I was unsuccessful writing the LINQ query and thought I would try to use a SQL View.

Created the view (17,000 rows grouped down to 5000 rows).
The Views consists of 'LastNameStartsWith', 'Total', 'AcademicPeriod', 'ProgramCode'.
Added the View to the .edmx model
Wrote the following queries.

As Linq;

public 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; 
}

When ran as sql with parameters filled in MSSMS the view filters down to 25 rows reporting the count of occurrences. A-Z, with X not reported as there are no lastnames starting with X.

If a breakpoint is set on return query;, it correctly reports an entity count of 24, but each entity is identical. LastNamesStartsWith = 'A' and Total = 21.

So it seems like the filtering is working but with a bug. Each entity should be unique A-Z.

If I add to the .Where like so


where (s.programCode == progCode) && (s.academicPeriod == cycleID) && (s.LastNameStartsWith == "B");

A correct return is generated. B, 35, 200890, BSED-ELED-ED

When using the Linq return elements, I get one measly element to use. LastNameStartsWith = "A" and Total = 21. But again B - Z and there totals are not returned. In the Domain Services Class, it did report that the elements count was 24. Where did they all go. I should have gotten 23 more elements even if they were all reporting A and 21.

So is there something special one should know when working with views and Linq?

Here the designer for the Entity Model


[assembly: global::System.Data.Objects.DataClasses.EdmSchemaAttribute()]

// Original file name:
// Generation date: 2/21/2010 10:25:00 PM
namespace 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();
    }
}

And here is the client side code calling the domain service and entity model.



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);
                }
            }

This same problem is being asked here, but through a different approach.

解决方案

When working with views using Entity Framework, make sure the entity key gets set correctly. This is often overlooked since views don't have primary keys EF tries to infer the key and it is often incorrect. Having an incorrect entity key can result in inconsistent and odd query results.

这篇关于在域服务类和实体数据模型中使用Linq查询SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

在域服务类和实体数据模型中使用Linq查询SQL视图 [英] Querying a SQL View, with Linq in a Domain Services Class and Entity Data Model

问题描述

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

在域服务类和实体数据模型中使用Linq查询SQL视图 [英] Querying a SQL View, with Linq in a Domain Services Class and Entity Data Model

问题描述

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭