2sxc | SQL Datasource - LINQ Filter Query

Question

我有一个SQL DataSource设置，可以从标准DNN'文件'表中获取某些扩展名的所有文档，但我想在要显示的文件类别上添加额外的规范但不确定如何最好地进行。请参阅下面的我当前的SQL DataSource代码：

@using ToSic.Eav.DataSources
@functions
{
    // Default data initialization - should be the place to write data-retrieval code
    // In the future when routing & pipelines are fully implemented, this will usually be an external configuration-only system
    // For now, it's done in a normal event, which is automatically called by the razor host in 2SexyContent
    public override void CustomizeData()
    {
        var source = CreateSource<SqlDataSource>();
        // source.TitleField = "EntityTitle"; // not necessary, default
        // source.EntityIdField = "EntityId"; // not necessary, default
        // source.ConnectionString = "...";   // not necessary, we're using the ConnectionStringName on the next line
        source.ConnectionStringName = Content.ConnectionName;
    // Special note: I'm not selecting * from the DB, because I'm activating JSON and want to be sure that no secret data goes out
    source.SelectCommand = "Select Top 10 FileId as EntityId, FileName as EntityTitle, PublishedVersion, UniqueId, FileName, Size as Size, Extension as Extension, CreatedOnDate as Date, Folder as Folder FROM Files WHERE PortalId = @PortalId AND Extension = 'docx' OR Extension = 'xlsx' OR Extension = 'pdf'";
    source.Configuration.Add("@PortalId", Dnn.Portal.PortalId.ToString());
    Data.In.Add("FileList", source.Out["Default"]);
    // enable publishing
    Data.Publish.Enabled = true;
    Data.Publish.Streams = "Default,FileList";
    }
}

我想将2SXC类别实体与DNN的选项卡/页面分类标签/类别同步特定DOC/EXCEL/PDF文件(已经通过2SXC ICACHE连接到2SXC类别(基于SQL DataSource，该应用程序通过与内容项目表连接连接的SQL DataSource，该数据通过加入分类_terms表，并随附内容项目表，并与内容项目标签表相连DNN选项卡表。

如何更正下面的linq/razor代码来过滤我的类别，以显示为分配给其的确切"服务"类别的文件。我将使用此过滤器与我想链接到2SXC类别的分类学标签"服务"(确切的匹配((已通过2SXC ICACHE已连接的ADAM文件(与DNN分类学术语'Services'？

@foreach (var file in AsDynamic(Data.In["FileList"]).Where(i => 
        (i.Category as List<dynamic>).Any(c => c.EntityId == FileList.EntityId)))
        {
            <li>@file.FileName</li>
        }

我已经详细介绍了https://github.com/2sic/2sxc/wiki/wiki/dotnet-query-linq上的Wiki注释SQL数据源模板。

欢呼...

Answer 1

我相信我们已经通过邮件解决了这个问题。

一个次要建议：如果您使用DNNSQLDATASOURCE而不是SQLDATASOURCE，则已经为当前DNN具有正确的连接字符串。另请参见http://2sxc.org/en/docs/feature/feature/4670以及https://github.com/2sic/2sic/2sxc/wiki/wiki/dotnet-datasources-datasources-and-datasources-allces-allces-allces-allces-allces-allces-allces-allces-al

是的，我需要的过滤器如下所示：

@using ToSic.SexyContent
    @{
    // all QandA items
    var all = AsDynamic(App.Data["QandA"].List);
    // the filter value, can be set in template
    // but usually you would either get it from url with Request["urlparam"]
    // or from a setting in the view, using Content.Category
    var currentCat = "Business";
    // filter, find any which have the current category
    var filtered = all
    .Where(p => (p.Categories as List<DynamicEntity>).Any(c => AsDynamic(c).Name == currentCat)); 
}
<div class="clearfix">
        @foreach (var q in filtered)
        {
            <div class="sc-element" data-tags="@String.Join(",", ((List<DynamicEntity>)q.Categories).Select(a => AsDynamic(a).EntityId))">
                @Edit.Toolbar(Content)
                <div class="col-md-12">
                    <div class="">
                        <a href="@q.Link" class="">
                            <span class="">@q.Link</span>
                        </a>
                        <p>@q.Title</p>
                    </div>
                </div>
            </div>
        }
</div>

再次感谢！