Scott Harrison - The golden apples
Henry Parker's Robot Wars

ASP.Net, ADO, MS Access
C# - How to return an IQueryable dataset using an Asynchronous Call.

C# - How to return an IQueryable dataset using an Asynchronous Call.

(25 hits)
Hits=19 My goal is to reduce the amount of linq required to access data in the database. One way to reduce the amount of linq code is use the eager loading class references produced by Entity Framework for a database table. An ScheduleEvent table has foreign keys for the Employee AddressBook and the Customer AddressBook records. The foreign keys relationship is used by EntityFramework to create a virtual EmployeeAddressBook and a virtual CustomerAddressBook. The Schedule Events are returned as an IQueryable type. This allows me to foreach the result of the query results.

The Unit of Work repository ScheduleEventRepository uses eager loading to return the parent object of the entity framework schedulevent class: EmployeeAddressBook. The ScheduleEvent has a virtual reference to an Employee AddressBook and a Customer AddressBook. The EmployeeAddressBook and CustomerAddressBook are virtual meaning, they must be included to be loaded by the base class method GetObjectsAsync where I pass the lambda expression and the table name to Include for eager loading.

Use Task.ContinueWith to work within the state of the Task. The messagebox displays the object within the state of the Task. If you try to access the FormData outside of the continueWith, the run time will error saying the object is null.

public IQueryable<T> GetObjectsAsync(Expression<Func<T, bool&g;t> predicate,string includeTable="")
        {
            IQueryable<T> result = _dbContext.Set<T>().Where(predicate);
            if (includeTable != "")
            {
                result=result.Include(includeTable);
            }
                
            
                return result;
        }

The ScheduleEventRepository class returns an IQueryable data set asychronously. The base.GetObjectsAsync calls the repository parent class with the "EmployeeAddressBook" string to be eagerly loaded. The ScheduleEvent list is returned as Queryable to the Unit Test using the ToListAsync() and .AsQueryable properties on the resultset. The output is the Employee Address Book name, the Schedule Event date and duration time.

Output

dan brown Date: 1/20/2017 10:00:00 AM Duration: 1

Unit Test Method


[TestMethod]
        public void TestGetScheduleEvents()
        {
            UnitOfWork unitOfWork = new UnitOfWork();
            int employeeAddressId = 3;
            Task<IQueryable<ScheduleEvent>> resultTask = Task.Run<IQueryable<ScheduleEvent>>(async () => await unitOfWork.scheduleEventRepository.GetScheduleEvents(employeeAddressId));

            IList<ScheduleEvent> list = new List<ScheduleEvent>();
            foreach (var item in resultTask.Result)
            {
                Console.WriteLine($"{item.EmployeeAddressBook.Name} Date: {item.EventDateTime} Duration: {item.DurationMinutes}");
                list.Add(item);
            }
            var Employee = list.Where(e => e.EmployeeAddressBook.Name == "dan brown").FirstOrDefault();

            Assert.IsTrue(Employee!=null);
        }

Entity Framework, database first, ScheduleEvent Class


 public partial class ScheduleEvent
    {
        public long Id { get; set; }
        public Nullable<long> EmployeeAddressId { get; set; }
        public Nullable<System.DateTime> EventDateTime { get; set; }
        public Nullable<long> ServiceId { get; set; }
        public Nullable<long> DurationMinutes { get; set; }
        public Nullable<long> CustomerAddressId { get; set; }
    
        public virtual AddressBook CustomerAddressBook { get; set; }
        public virtual AddressBook EmployeeAddressBook { get; set; }
        public virtual ServiceInformation ServiceInformation { get; set; }
    }

ScheduleEventRepository


public async Task<IQueryable<ScheduleEvent>> GetScheduleEvents(int employeeAddressId)
        {
           
            var list = await base.GetObjectsAsync(e => e.EmployeeAddressId == employeeAddressId, "EmployeeAddressBook").ToListAsync();
           
            return list.AsQueryable<ScheduleEvent>();
           
        }

AddressBook Clas

public class AddressBook
{
  public long AddressId { get; set; }
        public string Name { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Company { get; set; }
        public string CellPhone { get; set; }
        public string MailingCity { get; set; }
        public string MailingState { get; set; }
        public string MailingAddress { get; set; }
        public string MailingZipcode { get; set; }
        public string BillingCity { get; set; }
        public string BillingState { get; set; }
        public string BillingZipcode { get; set; }
        public string BillingAddress { get; set; }
}

ContinueWith


 private void cmdSelect_Click(object sender, EventArgs e)
        {
            int addressId = 0;
            AddressBook formData = new AddressBook();

            if (dataGridViewAddressBook.SelectedRows.Count != 0)
            {
                DataGridViewRow row = this.dataGridViewAddressBook.SelectedRows[0];
                addressId = Int32.Parse(row.Cells["AddressId"].Value.ToString());


                Task<AddressBook> resultTask = Task.Run<AddressBook>(async () => await unitOfWork.addressBookRepository.GetObjectAsync(addressId));

                Task continueTask & resultTask.ContinueWith(
               query =>
               {
                   formData.AddressId = query.Result.AddressId;
                   formData.Name = query.Result.Name;
                   formData.FirstName = query.Result.FirstName;
                   formData.LastName = query.Result.LastName;
                   formData.Company = query.Result.Company;
                   formData.PrimaryPhoneId = query.Result.PrimaryPhoneId;
                   formData.MailingCity = query.Result.MailingCity;
                   formData.MailingState = query.Result.MailingState;
                   formData.MailingAddress = query.Result.MailingAddress;
                   formData.MailingZipcode = query.Result.MailingZipcode;
                   formData.BillingCity = query.Result.BillingCity;
                   formData.BillingState = query.Result.BillingState;
                   formData.BillingZipcode = query.Result.BillingZipcode;
                   formData.BillingAddress = query.Result.BillingAddress;
                   formData.Type = query.Result.Type;
                   formData.PeopleXrefId = query.Result.PeopleXrefId;
                   formData.ProductKey = query.Result.ProductKey;
                   formData.Fax = query.Result.Fax;
                   formData.PrimaryShippedToAddressId = query.Result.PrimaryShippedToAddressId;
                   formData.PrimaryEmailId = query.Result.PrimaryEmailId;
                   MessageBox.Show(formData.Name);
               });


                Task.WaitAll(resultTask);

             



            }
....

...<<<Register to correspond>>> ...

Members : 152
Name:
Email:

Register to View

Help