Query Expression in Dynamics 365

Hello Guys,

In my previous blog i have shown you how you can make connection between D365 and C# Client. Today i'm going to show you how you can retrieve data from Dynamics. But before if you are new to our blog then do follow us to know more about Dynamics and Power Platform. Also if you like our blog then please comment and share this blog with your friends.

While working with C# client we may have come across such scenario where we need to retrieve data from the Dynamics and need to display those data either in console screen or in grid of windows application or in a web api.

To retrieve data from the dyanamics in c# application we are using a class known as QueryExpression which is present in Microsoft.Xrm.Sdk.Query namespace. 

To know more about Query Expression click here.

In this blog i will show you how you can write various query expression just like we were writing a query in Structure Query Language. To show demonstration i have made three entities in Dynamics CRM which are as below:
  • Employee 
  • City
  • Department

We will be working with our previous project that is D365Connector which we have created in visual Studio 2017. We have made one method named GetEmployee in CRMHelper.cs file. This method will give us data which we are going to retrieve using Query Expression. So we will be wrting various queries here.
    
1.Display all records of Employee.


Here in above example,
    
EntiyName :- Schema name of the entity.
ColumnSet :- It indicates which columns you want to display. If you want to    display all which is similar to (*) in Sql then you have to use boolean value true.
NoLock       :- Indicates that no database locks are issued against the data that would prohibit other transactions from modifying the data in the records returned from the query.

we will call this method and write the code for displaying data fetched through the query.


2. Display all records of Employee along with selected columns.


3. Display all records of Employee whose Lastname is "Gupta"


4. Display all records of the employees whose salary is greater than 25000.


5. Display all records of the employees whose salary is less than 25000 or equal to 25000.


6. Display records of employees whose first name contains only four letters.


7. Display records of employees whose first name starts with "A".


8. Display records of employees whose first name starts with "A","H", or "R".


9. Display records of employees whose first name does not starts with "A","H" or "R".


10. Display records of employees whose salary is between 30000,50000.


11. Display records of employees whose last name is either "Gupta" or "Kaur".


12. Display records of employees whose last name is "Gupta" and Salary is greater than 40000.


13. Display all records of the employee in ascending order of the first name.


14. Sort employees by last name and then by their salary.


For displaying salary we need to use Money class casting.



15. Display records of employee along with their department and department location.

Here we need to join employee entity with department entity and to do so we will be using Link Entities.



16. Display all records of employee who belongs to "IT" department.


17. Display records of the employee along with their departments. If employee does not belong to any department then display blank in department.

here we need to use left join to achieve this.


In this way you can use Query Expression to get data from the Dynamics 365. In our upcoming blog i will show how you can retrieve data using paging concept in D365.

Hope it helps...







Comments

Post a Comment

Popular posts from this blog

Read Only Sub Grid & Editable Sub grid in Dynamics 365

Image
Hello Guys, In my previous blogs we have seen Duplicate Detection in Dynamics 365 where we covered how you can use OOB feature of D365 to detect duplicate records. Today we will see how we can implement editable sub grid.  But before if you are new to our blog then do follow us to know more about Dynamics and Power Platform. Also if you like our blog then please comment and share this blog with your friends. In Dynamics CRM we have seen many entities sharing parent child relationships and we can create records of child from the parent page. For example Account and Contact Entity. We can create a new contact entity from the Account Form with the help of Sub Grid control. We can also create sub grid for custom entity and can utilize it in our own way. We will be using Department and Employee Entity for demonstration. Below are the steps: Step 1:- Login to Dynamics 365 with credentials. Step 2:- Click on Advance Settings Gear icon. Navigate to Settings -> Solution -...
Read more

Hiding Sub Grid buttons using Ribbon Workbench and JavaScript in Dynamics 365.

Image
Hello Guys, In my previous blog we have seen how we can hide buttons of the sub grid using Ribbon Workbench customization and we have hidden buttons of employee sub-grid.Today we will see how we do the same thing using JavaScript based on some criteria.  But before if you are new to our blog then do follow us to know more about Dynamics and Power Platform. Also if you like our blog then please comment and share this blog with your friends. Here we will be working with the same example which we have worked in our previous blog. As you can see here, we have IT department record is opened and it contains employee sub-grid which is having four records. Currently we have made Add Employee button hidden. We will make it visible and will hide based on condition i.e. if total no. of employees is ten then we will hide this button.Which means if sub-grid contains less than 10 records then you can add employee and once it's count will be 10 then we will hide the button. So first of all we wil...
Read more

Creating, Modifying and Copying Security Roles in Dynamics 365.

Image
  Hello Guys, In our previous  blog  we have seen basics of Security. Today we will see various ways for creating security roles in Dynamics 365. Do follow us to know more about Dynamics and Power Platform. Also if you like our blog then please comment and share this blog with your friends. There are two ways for creating a security role. 1. Copying existing role and modify it. 2. Create a new role from the scratch. First we will see how to create security role using copying and modifying method. Navigate to  https://admin.powerplatform.microsoft.com , select your environment and click on settings. Now expand Users + Permissions and click on Security Roles. As you can see, we have existing security roles created by default. Now we will be creating one more role. So select the security role which you want to create copy of and click on Copy Role. Specify New Name for the Role and click on Copy. As you can see our new role is created successfully. Now select the role a...
Read more