locked
How to execute two EF linq query with one db round trip RRS feed

  • Question

  • User1183902823 posted

    when we work with ado.net then we can pass multiple sql separated by comma. here is one example.

    SqlConnection connection = new SqlConnection();
    SqlCommand command = new SqlCommand();
    connection.ConnectionString = connectionString; // put your connection string
    command.CommandText = @"
         update table
         set somecol = somevalue;
         insert into someTable values(1,'test');";
    command.CommandType = CommandType.Text;
    command.Connection = connection;
    
    try
    {
        connection.Open();
    }
    finally
    {
        command.Dispose();
        connection.Dispose();
    }

    i like to know how could i construct EF linq based query which will fetch data from multiple table but there is no relation between two table. so we can not perform join.

    so show me a example where EF will fetch data from student and product table without any join with one db round trip. is it possible which was possible by ado.net as per my above example.

     var query = from data in context.Student

                    orderby data.name

                    select data;

     var query = from data in context.Product

                    orderby data.name

                    select data;

    when two above query will run then two database round trip will occur or one ?

    i need to fetch data from two table called student and product with one db round trip just like my above EF linq query.

    if possible then discuss with sample code. thanks

    Friday, December 8, 2017 8:42 AM

All replies

  • Friday, December 8, 2017 11:58 AM
  • User-707554951 posted

    Hi tridip1974,

    There is a lot of difference between building a query and executing it. Calling methods like ToList, ToArray, First, Single and ... will execute the query you have built since calling methods like Where, Union, Except and ... are just building up the query for you.

    Scenario 1:

    var query1 = ( from a in this.Context.Blah select a); // you have a query
    
    var query2 = ( from b in this.Context.Yada select b); // you have another query
    
    var query3 = (query1.Union(query2)); // you build a new query
    
    var result = query3.ToList(); // you execute a query and get result

    Scenario 2:

    //this builds a query and execute it and get result from database
    
    var query1 = ( from a in this.Context.Blah select a).ToList(); // you execute a query
    
    //this builds a query and execute it and get result from database
    
    var query2 = ( from b in this.Context.Yada select b).ToList(); // you execute another query
    
    //this builds a query and execute it but it does not hit database because you are working
    
    //with IEnumerable here not IQueryable
    
    var query3 = (query1.Union(query2)); // you build a new query
    
    var result = query3.ToList(); // you execute another query and get result

    Scenario 2 execute 3 query and have 2 round trips to database while the first one just fetch what you want with one round trip.

    Best regards 

    Cathy

    Monday, December 11, 2017 5:37 AM