none
Querying GUID Fields via LINQ Statements off EF Models RRS feed

  • Question

  • We provide our client with a "Query Builder", allowing them to select a field, an operator, and a value. They may select many such combinations "where clauses" to create their query. However, the vast majority of all our tables (and subsequently all our models in EF) are keyed on GUID fields.

    We have the need to query GUIDs with the following operators: Equals, Not Equals, Contains, Starts With, Ends With, etc. however, IQueryable LINQ does not provide these options for a GUID field (i.e.: var query = (from o in context.Person select o).Where(o.GUID.Contains("ARU456-")) && (... etc))

    I have found that I CAN do this if I convert the original Query from IQueryable to IEnumerable (i.e.: var query = (from o in context.Person select o).AsEnumerable(). And then I can run: query.Where(GUID.ToString().Contains("ARU456-")).

    BUT ... here is my problem:

    1. If I take the IEnumerable route, I assume this means that the original query (var query = (from o in context.Person select o).AsEnumerable()) is run and "all" rows are brought back and placed into an Enumerable List of objects. Is that correct? Because our DB could have 15 - 50 million rows. So that is a large db query, plus a large memory block.

    2. The Person Object consists of multiple Lists of Objects such as Person.Vehicles = new List<Vehicles>(); So, once I have pushed the query to an Enumerable, I cannot place the following in the LINQ Where clause: query.Where(GUID.ToString().Contains("ARU456-") && Vehicles.VehicleId = 123). -- because it no longer recognizes that Vehicles is an object of Person in the Enumerated List. So, how do I query it like I have stated here?

    Ultimately, if #1 is true, we cannot go the route of #2, as the query statement and memory allocation will be too big. If we stay with IQueryable ... Can I write Predicates or Func<o,type> statements that allow me to query a GUID with more than = and <> (i.e. Contains, Starts With, etc.).

    I realize this is a big problem and complex, but I have been trying to resolve this now off and on for months. And I get very close, but cannot seem to incorporate all of our requirements into a resolution.

    Thank you

    Thursday, August 13, 2015 8:46 PM

Answers

  • Hello Danny,

    >> however, IQueryable LINQ does not provide these options for a GUID field (i.e.: var query = (from o in context.Person select o).Where(o.GUID.Contains("ARU456-")) && (... etc))

    What EF version you are using? I suggest you could install the 6.1.3 version and for the GUID type filed, you could now call its ToString Method even in a IQueryable query as below:

    using (CFContext db = new CFContext())
    
                {
    
                    //db.Database.CreateIfNotExists();
    
                    var result = (from o in db.Orders
    
                                  where o.ID.ToString().Contains("e009f168-8b75-4413-9836")
    
                                  select o).ToList();
    
                }

    This is the model class:

    public class Order
    
        {
    
            public Guid ID { get; set; }
    
            public string Name { get; set; }
    
        }

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Friday, August 14, 2015 2:52 AM
    Moderator

All replies

  • Hi Danny,

    You are asking question about Entity Framework. I am going to help move thread to Entity forum for effective response.

    Thank you for understanding.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 14, 2015 1:47 AM
    Moderator
  • Hello Danny,

    >> however, IQueryable LINQ does not provide these options for a GUID field (i.e.: var query = (from o in context.Person select o).Where(o.GUID.Contains("ARU456-")) && (... etc))

    What EF version you are using? I suggest you could install the 6.1.3 version and for the GUID type filed, you could now call its ToString Method even in a IQueryable query as below:

    using (CFContext db = new CFContext())
    
                {
    
                    //db.Database.CreateIfNotExists();
    
                    var result = (from o in db.Orders
    
                                  where o.ID.ToString().Contains("e009f168-8b75-4413-9836")
    
                                  select o).ToList();
    
                }

    This is the model class:

    public class Order
    
        {
    
            public Guid ID { get; set; }
    
            public string Name { get; set; }
    
        }

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Friday, August 14, 2015 2:52 AM
    Moderator
  • Awesome ... we are limited by government restrictions on when we can upgrade ... but I will upgrade locally to test this, and if indeed it works, we will write a proposal to upgrade to some of the latest .net and supported frameworks.

    Thanks you,

    Danny

    Monday, November 2, 2015 10:44 PM
  • Awesome ... we are limited by government restrictions on when we can upgrade ... but I will upgrade locally to test this, and if indeed it works, we will write a proposal to upgrade to some of the latest .net and supported frameworks.

    Thanks you,

    Danny

    I never considered Linq to be a stops all and ends all with EF. You could also consider using Entity-SQL, which is much is much like T-SQL when it comes to querying the EF virtual model. You can still use the objects on the EF virtual model too as DTO(s).

    https://msdn.microsoft.com/en-us/library/vstudio/bb399560(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/vstudio/bb399359(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/vstudio/bb738684(v=vs.100).aspx

    It's been more than a few times when I have used E-SQL to query when Linq and EF together couldn't do it.

    Tuesday, November 3, 2015 4:28 AM