String Literals and POCO TypedListDAO

Posts   
 
    
Posts: 3
Joined: 19-Mar-2021
# Posted on: 22-Mar-2021 23:05:34   

Environment: SQLServer / Azure .net Core 3.1 llbl: 5.6

Below is a function that I wrote to pull up customers that have not set up any appointments. It works fine. I have 2 questions.

1) Since the customers don't have any appointments and I am trying to get their Facility Information by the appointments that they have made these are what I call unaffiliated customers. For the FacilityName, FacilityCity and FacilityState I want to hard code in the following: FacilityName = "Unaffiliated" FacilityCity = "No Appointments" FacilityState = "Never Booked"

I want these to be part of the select statement but I can not figure out how to hard code them in.

2) In the method below I am using TypedListDAO. I have a class CustomerListByFacilityList I would like TypedListDAO to return a list to me containing records of type CustomerListByFacilityList. Is this possible? If so can you give me an example on how to set this up so it works. I tried Datatable but it was always empty. I think I am missing 1 small piece that would bring this all together. Right now we have our own static mapper function but I don't think that is necessary and if LLBL can return the a List of CustomerListByFacilityList then we would not have to loop through the data using the mapper, hence it would be faster.

        /// <summary>
        /// Gets a list of all customers (that are not affliliated with and facilities)
        /// </summary>
        /// <param name="facilityManagerUid">string</param>
        /// <param name="activeFlag">string</param>
        /// A = Returns all active customers
        /// I = Returns all inactive customers
        /// B = Returns both active and inactive
        /// <param name="userTypeId">int</param>
        /// This rotuine is only for Admins and Facility Managers
        /// <returns>
        /// A list of customers
        /// </returns>
        private async Task<object> GetFacilityCustomerListUnaffiliated(int userTypeId, string activeFlag)
        {
            if (userTypeId != 1 && userTypeId != 3) return null;
            int activeValue = (activeFlag.Equals("A")) ? 1 : (activeFlag.Equals("I")) ? 0 : -1;
            var qf = new QueryFactory();

            // ***** IMPORTANT *****
            //
            // This is a dynamic query.  If you change the order of the fields or add/delete fields in this query then you must change the WithProjector too.
            //                           MapToCustomerListByFacilityList must also be updated to match the ordering and the GetFacilityCustomerList and
            //                           GetFacilityCustomerListUnaffliated must also match.
            //
            // ***** IMPORTANT *****
            var q = qf.Create().Select(CustomerFields.Uid.Source("cust").As("UID"), CustomerFields.Active.Source("cust").As("Active"),
                                       CustomerFields.FirstName.Source("cust").As("FirstName"), CustomerFields.LastName.Source("cust").As("LastName"),
                                       CustomerFields.Address1.Source("cust").As("Address1"), CustomerFields.Address2.Source("cust").As("Address2"),
                                       CustomerFields.City.Source("cust").As("City"), CustomerFields.StateCode.Source("cust").As("StateCode"),
                                       CustomerFields.ZipCode.Source("cust").As("ZipCode"), CustomerFields.Email.Source("cust").As("Email"),
                                       CustomerFields.PhoneNumber.Source("cust").As("PhoneNumber"))
                    .From(qf.Customer.As("cust"))
                    .Where(CustomerFields.UserTypeId.Source("cust") == 2);
            if (activeValue != -1)
            {
                q.AndWhere(CustomerFields.Active.Source("cust") == activeValue);
            }
            q.WhereNotExists(qf.Create().Select(AppointmentFields.CustomerUid.Source("appt").As("CustomerUid"))
                               .From(qf.Appointment.As("appt"))
                               .Where(CustomerFields.Uid.Source("cust") == AppointmentFields.CustomerUid.Source("appt")));

            q.OrderBy(CustomerFields.FirstName.Source("cust").Ascending())
            .OrderBy(CustomerFields.LastName.Source("cust").Ascending())
            .Distinct()
            .WithProjector(r => new CustomerListByFacilityList
            {
                UID = (string)r[0],
                Active = (bool)r[1],
                FirstName = (string)r[2],
                LastName = (string)r[3],
                Address1 = (string)r[4],
                Address2 = (string)r[5],
                City = (string)r[6],
                State = (string)r[7],
                ZipCode = (string)r[8],
                Email = (string)r[9],
                PhoneNumber = (string)r[10]
            });

            var results = new TypedListDAO().FetchQuery(q);
            if (results != null)
            {
                return (results.MapToCustomerListByFacilityList());
            }
            return null;
        }

    }
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 23-Mar-2021 07:28:48   

Hi Susan,

There are many ways you could accomplish this. Since you are basically doing a entity fetch with extra projection I would use the .Select method with explicit DTO constructor like in (basic example):

[TestMethod]
public void QueryFactoryWithDTOProyection1()
{
    var adapter = new DataAccessAdapter();

    var name = "ALFKI";

    var qf = new QueryFactory();
    var q = qf.Customer
        .Where(CustomerFields.CustomerId.Equal(name))
        .Select(() => new CustomerDTO() 
            {
                CustomerId = CustomerFields.CustomerId.ToValue<string>(),
                CompanyName = CustomerFields.CompanyName.ToValue<string>(),
                ContactName = CustomerFields.ContactName.ToValue<string>(),
                Country = CustomerFields.Country.ToValue<string>(),
                Extra1 = "This is my extra 1",
                Extra2 = "This is my extra 2"
            }
        );

            
    var customers = adapter.FetchQuery(q);

    Assert.IsTrue(customers.Count > 0);
    Assert.AreEqual(customers.First().CustomerId, "ALFKI");
    Assert.AreEqual(customers.First().Extra1, "This is my extra 1");
}

public class CustomerDTO
{
    public string CustomerId { get; set; }

    public string CompanyName { get; set; }

    public string ContactName { get; set; }
            
    public string Country { get; set; }

    public string Extra1 { get; set; }

    public string Extra2 { get; set; }
}

... additionally, in the way you are doing it ( WithProjector ) you could also pass literals. Example:

[TestMethod]
public void QueryFactoryWithDTOProyection3()
{
    var qf = new QueryFactory();
    var q = qf.Create()
        .Select(
                CustomerFields.CustomerId,
                CustomerFields.CompanyName,
                CustomerFields.ContactName,
                CustomerFields.Country
        )
        .Where(CustomerFields.CustomerId.Equal(name))
        .WithProjector(r => new CustomerDTO
        {
            CustomerId = (string)r[0],
            CompanyName = (string)r[1],
            ContactName = (string)r[2],
            Country = (string)r[3],
            Extra1 = "This is my extra 1",
            Extra2 = "This is my extra 2"
        });


    var customers = adapter.FetchQuery(q);

    Assert.IsTrue(customers.Count > 0);
    Assert.AreEqual(customers.First().CustomerId, "ALFKI");
    Assert.AreEqual(customers.First().Extra1, "This is my extra 1");
}
David Elizondo | LLBLGen Support Team
Posts: 3
Joined: 19-Mar-2021
# Posted on: 23-Mar-2021 07:44:10   

How do I get the adaptor? I don't appear to have access to it that is why I am using TypedListDAO. Is there a way to make the TypedListDAO return a list of CustomerListByFacilityList instead of a List of objects. Right now I get back a list of objects and it has to be mapped to CustomerListByFacilityList. I want to remove that loop through the data if I can.

Thanks, Susan

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Mar-2021 09:57:58   

David showed an example using Adapter, you're using selfservicing, so it needs a little adjustment.

First, I'm going to rework your query to what does the same thing but with much less code. As you have a UID property in the CustomerListByFacilityList which doesn't match the name of the field in the CustomerFields class, I can't use .Select<CustomerListByFacilityList, CustomerFields>() (see documentation ), so I've written them out.

var qBase = qf.Customers
            .Where(CustomerFields.UserTypeId==2);
if (activeValue != -1)
{
    qBase.AndWhere(CustomerFields.Active == activeValue);
}
qBase.WhereNotExists(qf.Create().Select(AppointmentFields.CustomerUid)
                   .From(qf.Appointment)
                   .Where(CustomerFields.Uid == AppointmentFields.CustomerUid));
qBase.OrderBy(CustomerFields.FirstName.Ascending(), CustomerFields.LastName.Ascending())
     .Distinct();
var q = qBase.Select(()=> new CustomerListByFacilityList()
{
    UID = CustomerFields.Uid.ToValue<Guid>(),
    Active = CustomerFields.Active.ToValue<bool>(),
    FirstName = CustomerFields.FirstName.ToValue<string>(), 
    CustomerFields.LastName.ToValue<string>(),
    CustomerFields.Address1.ToValue<string>(),
    CustomerFields.Address2.ToValue<string>(),
    CustomerFields.City.ToValue<string>(),
    CustomerFields.StateCode.ToValue<string>(),
    CustomerFields.ZipCode.ToValue<string>(),
    CustomerFields.Email.ToValue<string>(),
    CustomerFields.PhoneNumber.ToValue<string>(),
    FacilityName = "Unaffiliated", 
    FacilityCity = "No Appointments", 
    FacilityState = "Never Booked"
});

return new TypedListDAO.FetchQueryAsync(q);

You don't need all the aliasing with .Source() and .As() as you're not joining the same entity twice. I used qBase as the Dynamic, untyped query and q as the typed query. As you pass that to FetchQuery, result is a List<CustomerListByFacilityList>, and you can just return that. No need for the object type. Be sure to call the Async variant as you're in an async method (no need to await as it's the last call).

As you can see, I've added the hardcoded values in the projection.

An easier alternative would be to generate the query. You can generate this by creating a TypedList from Customers in the designer. Then don't add the 3 fields that need a hardcoded value, set the target type to POCOWithQuerySpecQuery, and create a partial class for the generated poco class where you define 3 properties, namely the Facility* fields, so you can define their values hard-coded.

Hope this helps

Frans Bouma | Lead developer LLBLGen Pro
Posts: 3
Joined: 19-Mar-2021
# Posted on: 23-Mar-2021 12:53:03   

Otis,

I implemented your changes but I kept getting an error because the query was pulling back all of the Appointment fields and it would say that it could not bind a multi part expression. I finally got it to work and return the correct type without having to go through a mapper but I had to have all of the source and as stuff in there. Here is a copy:

        private async Task<object> GetFacilityCustomerListUnaffiliated(int userTypeId, string activeFlag)
        {
            if (userTypeId != 1 && userTypeId != 3) return null;
            int activeValue = (activeFlag.Equals("A")) ? 1 : (activeFlag.Equals("I")) ? 0 : -1;
            var qf = new QueryFactory();
            var qBase = qf.Create().Select(CustomerFields.Uid.Source("cust").As("UID"), CustomerFields.Active.Source("cust").As("Active"),
                                       CustomerFields.FirstName.Source("cust").As("FirstName"), CustomerFields.LastName.Source("cust").As("LastName"),
                                       CustomerFields.Address1.Source("cust").As("Address1"), CustomerFields.Address2.Source("cust").As("Address2"),
                                       CustomerFields.City.Source("cust").As("City"), CustomerFields.StateCode.Source("cust").As("StateCode"),
                                       CustomerFields.ZipCode.Source("cust").As("ZipCode"), CustomerFields.Email.Source("cust").As("Email"),
                                       CustomerFields.PhoneNumber.Source("cust").As("PhoneNumber"))
                        .Where(CustomerFields.UserTypeId.Source("cust") == 2);
            if (activeValue != -1)
            {
                qBase.AndWhere(CustomerFields.Active.Source("cust") == activeValue);
            }
            qBase.WhereNotExists(qf.Create().Select(AppointmentFields.CustomerUid.Source("appt").As("CustomerUid"))
                               .From(qf.Appointment.As("appt"))
                               .Where(CustomerFields.Uid.Source("cust") == AppointmentFields.CustomerUid.Source("appt")));

            qBase.OrderBy(CustomerFields.FirstName.Source("cust").Ascending())
            .OrderBy(CustomerFields.LastName.Source("cust").Ascending())
            .Distinct();
            var q = qBase.Select(() => new CustomerListByFacilityList()
            {
                UID = CustomerFields.Uid.Source("cust").As("UID").ToValue<string>(),
                Active = CustomerFields.Active.Source("cust").As("Active").ToValue<bool>(),
                FirstName = CustomerFields.FirstName.Source("cust").As("FirstName").ToValue<string>(),
                LastName = CustomerFields.LastName.Source("cust").As("LastName").ToValue<string>(),
                Address1 = CustomerFields.Address1.Source("cust").As("Address1").ToValue<string>(),
                Address2 = CustomerFields.Address2.Source("cust").As("Address2").ToValue<string>(),
                City = CustomerFields.City.Source("cust").As("City").ToValue<string>(),
                State = CustomerFields.StateCode.Source("cust").As("StateCode").ToValue<string>(),
                ZipCode = CustomerFields.ZipCode.Source("cust").As("ZipCode").ToValue<string>(),
                Email = CustomerFields.Email.Source("cust").As("Email").ToValue<string>(),
                PhoneNumber = CustomerFields.PhoneNumber.Source("cust").As("PhoneNumber").ToValue<string>(),
                FacilityUID = Guid.Empty.ToString(),
                FacilityActive = true,
                FacilityName = "Unaffiliated",
                FacilityStatus = "Approved",
                FacilityCity = "No appointments",
                FacilityState = "Never booked"
            });

            var results = new TypedListDAO().FetchQuery< CustomerListByFacilityList>(q);
            return results;
        }

Susan Conger

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 23-Mar-2021 18:35:22   

Good that you got it working. But as I see no reason to use all those aliases, please let us know if you want help tracing down the source of the error you got. If you are satisfied with the current resolution, then maybe the next time you bump into this, we can have a deeper look into it.

I'll close this thread for now, it will be automatically reopened if you reply back.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Mar-2021 09:41:49   

Susan, you have 2 times the projection in the code, which is unnecessary and results in a lot of code which you don't need. THe error you speak of might be a result of a missing alias but without the actual query that's been generated it's a bit hard to figure out if that's the case. In my test I didn't need an alias hence I suggested an alias free query. However, you can check which query is generated and correct it yourself, by using: https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm See the trace configuration to get detailed information about what's generated and executed. The less code you need, the better simple_smile

Frans Bouma | Lead developer LLBLGen Pro