You just need to do a sub query with the categories.
using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web.UI.WebControls;
namespace stackoverflow
{
public partial class BuscarCategoriaSubCategorias : System.Web.UI.Page
{
protected void Button1_Click(object sender, EventArgs e)
{
String valor = TextBox1.Text;
using (var ctx = new stackoverflowEntities())
{
var qrInSubCategoria = ctx.SubCategoria.AsQueryable();
if (!string.IsNullOrEmpty(valor))
{
qrInSubCategoria = qrInSubCategoria
.Where(C => C.Nome.ToUpper().Contains(valor.ToUpper()));
}
var qrInCategoria = qrInSubCategoria
.Select(SC => new
{
SubCategoria = SC,
Categoria = ctx.Categoria.FirstOrDefault(C => C.CategoriaId == SC.CategoriaId)
})
.Select(SC => new ResultadoCategSubCategoriaGrid
{
SubCategoriaId = SC.SubCategoria.SubCategoriaId,
SubCategoriaNome = SC.SubCategoria.Nome,
CategoriaId = SC.Categoria.CategoriaId,
CategoriaNome = SC.Categoria.Nome,
}).ToList();
GridView1.DataSource = qrInCategoria;
GridView1.DataBind();
}
}
}
[Serializable, NotMapped]
public class ResultadoCategSubCategoriaGrid
{
public int SubCategoriaId { get; set; }
public string SubCategoriaNome { get; set; }
public int CategoriaId { get; set; }
public string CategoriaNome { get; set; }
}
}
Aspx;
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="BuscarCategoriaSubCategorias.aspx.cs" Inherits="stackoverflow.BuscarCategoriaSubCategorias" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
<table>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Sub Categoria"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Buscar" />
</td>
</tr>
</table>
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</asp:Content>
Entity Model;
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace stackoverflow
{
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
public partial class stackoverflowEntities : DbContext
{
public stackoverflowEntities()
: base("name=stackoverflowEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public virtual DbSet<Categoria> Categoria { get; set; }
public virtual DbSet<SubCategoria> SubCategoria { get; set; }
}
}
Bank;
CREATE TABLE [dbo].[Categoria](
[CategoriaId] [int] IDENTITY(1,1) NOT NULL,
[Nome] [varchar](100) NOT NULL,
[DtInclusao] [datetime] NOT NULL,
CONSTRAINT [PK_Categoria] PRIMARY KEY CLUSTERED
(
[CategoriaId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert into Categoria values
('Calçados', getdate()),
('Roupas', getdate())
CREATE TABLE [dbo].[SubCategoria](
[SubCategoriaId] [int] IDENTITY(1,1) NOT NULL,
[Nome] [varchar](100) NOT NULL,
[CategoriaId] [int] NOT NULL,
CONSTRAINT [PK_SubCategoria] PRIMARY KEY CLUSTERED
(
[SubCategoriaId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into SubCategoria values
('havaianas',1),
('botas dakota',1),
('botas ramarim',1),
('botas jacomo',1),
('Blusa Inverno',2),
('Blusa Regata',2),
('Vestido',2)