3.0 Optimize Query-introduce view, 3.0 query and import View

3.0 Optimize Query-introduce view, 3.0 query and import 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.

<span style="font-size:18px;">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</span>

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.