c#. EF entity sql. How to get entity with related objects?

Question

I have made simple model for example.

public class Publisher
{
    public int Id { get; set; }

    public string Title { get; set; }

    public Address Location { get; set; }

    public virtual ICollection<Book> Books { get; set; }
}

public class Address
{
    public string Country { get; set; }
    public string City { get; set; }
    public string Street { get; set; }
    public string HouseNumber { get; set; }
}  


public class Book
{
    public int Id { get; set; }

    public string Title { get; set; }

    public string Author { get; set; }

    public int LanguageId { get; set; }

    public int? PublisherId { get; set; }
}

I need to get publishers with related books. I know how to do it using linq to entities. Is it possible to solve a problem using entity sql?

    public class CatalogContext : DbContext {...}

    public List<Publisher> GetByCity(string city)
    {
        var result = new List<Publisher>();
        string queryString;
            queryString = String.Format(@"SELECT VALUE row(a,b) 
                                        FROM CatalogContext.Publishers AS a 
                                        join CatalogContext.Books AS b on a.Id = b.PublisherId
                                        WHERE a.Location.City = '{0}'", city);
        var rows = ((IObjectContextAdapter)_context).ObjectContext.CreateQuery<DbDataRecord>(queryString).ToList();
        return ???
    }

Query returns required data but it's List<DbDataRecord> - list of pairs <publisher, book>. How to translate it to list of publishers with filled navigation property "Books"? Is it possible to write query which directly returns List<Publisher>?

You're selecting (a,b), which gives you , so wouldn't selecting (a) give you ? — Jon La Marr, Sep 18 '13 at 14:31
In this case I will receive related books only by additional query. — shoma13, Sep 18 '13 at 15:24

madd · Answer 1 · 2013-09-18T14:59:48.127

0

you can do the following:

var result = ObjectContext.Publishers.Include("Books").Include("Locations")
.Where(c => c.Location.City = "SOME_CITY").Select(c => c);

Include - basically joins the table.

Then you can drill down to books by doing the following:

var test = result[0].Books;

Why are you using direct sql command instead of Entity Framework code style?

edited Sep 18 '13 at 14:59

answered Sep 18 '13 at 14:34

madd

339
2
8

Thanks. But your solution use link to entities. My question is rather theoretic than practical. Real problem is much more difficult. Although it also can be solved using linq entity-sql based solution might be more understandable. – shoma13 Sep 18 '13 at 15:06

c#. EF entity sql. How to get entity with related objects?

1 Answers1