Htykuut

I'm using LINQ to execute a query on a List type variable with a large amount of data (over a million). For performance purposes I'm using IEnumerable to store the results but when I try to access it there is a slight delay.

Specifically I want to see if the query produced any results, but when I use the .Count() or .Any() functions the performance drops.

I read that for IEnumerable types the execution of the query happens at the time of need, hence the delay. Is there a way to see if the IEnumerable has elements inside it without having that much delay?

This is what I'm trying to run.

IEnumerable<Entity> matchingEntities = entities.Where(e => e.Names.Any(n => myEntity.Names.Any(entityName => entityName.CompareNameObjects(n))));

and here are my classes

public class Entity

{

   public string EntityIdentifier { get; set; }

   public List<Name> Names { get; set; }

}



public class Name

{

    public string FullName { get; set; }

    public string NameType { get; set; }



    public bool CompareNameObjects(Name name2)

    {

        return FullName == name2.FullName &&

               NameType == name2.NameType;

    }

}

entities is a list of all my objects and I want to check if myEntity has any Names identical with another entity in the set.

EDITED:

The data structure is similar to the 2 classes (Entity and Name). The entities are created by selecting all the entities, along with their names, from the database in XML format and then I convert the XML to a List as such:

List<Entity> entities = new List<Entity>();

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myCS"].ConnectionString))

{

    conn.Open();

    SqlCommand cmd = new SqlCommand("GetAllEntities", conn);

    cmd.CommandType = CommandType.StoredProcedure;



    string entitiesXml = "";

    using (SqlDataReader rdr = cmd.ExecuteReader())

    {

        while (rdr.Read())

        {

            entitiesXml += rdr["XmlString"].ToString();

        }

    }



    using (TextReader reader = new StringReader(entitiesXml))

        entities = (Entity)xmlSerializer.Deserialize(reader);



    conn.Close();

}



GetAllEntities (Stored Procedure):

declare @xmlString nvarchar(max) =(

    select  e.EntityIdentifier,

        (

            select  n.[Full Name]  as 'FullName', 

                    n.[Name Type]  as 'NameType'

            from tblNames n

            where e.EntityID=n.[Entity_ID]

            for xml path('Name'), type

        )

    from tblEntities e 

    order by e.EntityID

    for xml path('Entity')

) 

select @xmlString as XmlString

Basically, you should avoid getting all data from your database then filter it with C# code. It consumes a lot of effort.

However, for quick solution, you can improve performance by preparing your conditions in a Dictionary form firstly.

 // Let's say you have myEntity here

 var myEntity = new Entity();

 var entities = new List<Entity>();



 // You should prepare the list of name that you wanna to find before you do it so that you don't have to make it repeatedly for every iteration

 var names = myEntity.Names.Select(p=> p.FullName + p.NameType ).ToDictionary(p=>p, p=>p);



 IEnumerable<Entity> matchingEntities = entities.Where(e => e.Names.Any(n => names.ContainsKey(n.FullName + n.NameType)));

This is just an example that may give you more idea. You can improve much more. I hope it can help you