3.0 Optimize Query-introduce View
Under the efficient cloud platform, I am responsible for the students of the basic system. This includes addition, deletion, modification, and query of student information. The Student Statistics Section mainly displays various information in the form of charts. This blog introduces the simplified process from traditional table queries to View queries.
First, let's take a look at the relationship between student tables. The student table is associated with the dormitory, class, and foreign key of the Major. The class includes the major and level (graduate degree ).
Requirement
Query student information, including student ID, dormitory, class, Major, and major. We can see that these are all in other tables. Sometimes all student information is queried based on the class ID and professional ID.
In 2.0, the EF navigation attribute is used to query each item one by one. But it is still inconvenient. Change 3.0 to view. This comprehensive information is put together. It is easy to query.
The view is created based on student information, so the left join should be used for table connection.
The following table connection statements are used.
SELECT dbo.BasicClassEntities.ClassCode, dbo.BasicClassEntities.ClassName, dbo.BasicOrganizationEntities.OrganizationName, dbo.BasicOrganizationEntities.OrganizationID, dbo.BasicSchoolLevelEntities.LevelName, dbo.BasicRoomEntities.RoomName, dbo.BasicStudentEntities.StudentID, dbo.BasicStudentEntities.StudentNo, dbo.BasicStudentEntities.Name, dbo.BasicStudentEntities.UserCode, dbo.BasicStudentEntities.EntryTime, dbo.BasicStudentEntities.Origin, dbo.BasicStudentEntities.EntryPartyTime, dbo.BasicStudentEntities.Speciality, dbo.BasicStudentEntities.HealthCondition, dbo.BasicStudentEntities.ExamineeNumber, dbo.BasicStudentEntities.FatherName, dbo.BasicStudentEntities.MotherName, dbo.BasicStudentEntities.FatherPhone, dbo.BasicStudentEntities.MotherPhone, dbo.BasicStudentEntities.TrainDestination, dbo.BasicStudentEntities.Note, dbo.BasicStudentEntities.Status, dbo.BasicStudentEntities.Operator, dbo.BasicStudentEntities.TimeStamp, dbo.BasicStudentEntities.CreditCardNo, dbo.BasicStudentEntities.Sex, dbo.BasicStudentEntities.PoliticalStatus, dbo.BasicStudentEntities.PreviousName, dbo.BasicStudentEntities.Email, dbo.BasicStudentEntities.CellPhoneNumber, dbo.BasicStudentEntities.HomeTelephone, dbo.BasicStudentEntities.BirthPlace, dbo.BasicStudentEntities.HomeAddress, dbo.BasicStudentEntities.Nation, dbo.BasicStudentEntities.RoomID, dbo.BasicStudentEntities.DirectionID, dbo.BasicStudentEntities.ClassID, dbo.BasicStudentEntities.IsEnabled, dbo.BasicStudentEntities.Image, dbo.BasicDirectionEntities.DirectionID AS Expr1, dbo.BasicDirectionEntities.DirectionName, dbo.BasicDirectionEntities.DirectionCode, dbo.BasicRoomEntities.RoomID AS Expr2, dbo.BasicSchoolLevelEntities.SchoolLevelIDFROM dbo.BasicStudentEntities LEFT OUTER JOIN dbo.BasicDirectionEntities ON dbo.BasicStudentEntities.DirectionID = dbo.BasicDirectionEntities.DirectionID LEFT OUTER JOIN dbo.BasicClassEntities ON dbo.BasicStudentEntities.ClassID = dbo.BasicClassEntities.ClassID LEFT OUTER JOIN dbo.BasicRoomEntities ON dbo.BasicStudentEntities.RoomID = dbo.BasicRoomEntities.RoomID LEFT OUTER JOIN dbo.BasicOrganizationEntities ON dbo.BasicClassEntities.OrganizationID = dbo.BasicOrganizationEntities.OrganizationID LEFT OUTER JOIN dbo.BasicSchoolLevelEntities ON dbo.BasicOrganizationEntities.SchoolLevelID = dbo.BasicSchoolLevelEntities.SchoolLevelID
The only note is that left outer join in the preceding JOIN statement. Without complex queries, it will be much easier to develop and maintain in the future. Of course, the most important thing is not to build a view, but to improve your own shortcomings with great care. It cannot be the source power.